Re: timestamp and calculations.
От | Andreas Kretschmer |
---|---|
Тема | Re: timestamp and calculations. |
Дата | |
Msg-id | 20060720095745.GA26059@KanotixBox обсуждение исходный текст |
Ответ на | timestamp and calculations. (Thor Tall <tall_thor@yahoo.com>) |
Ответы |
Re: timestamp and calculations.
|
Список | pgsql-general |
Thor Tall <tall_thor@yahoo.com> schrieb: > Hi, > > I am new to postgres sql and have a problem with an > sql statement which I hope you can help me with. > > I want to do some calculation on the result of a > query. > > I have a table tb_test which contains a timestamp > column my_timestamp. > > My sql statement should display my_timestamp as "00", > "20", "40" where all timestamps with minutes between > "00" and until "20" should be displayed as "00" and > "20" until "40" as "20" and "40" > until "00" as "40" Something like this: test=# select * from times; t --------------------- 2006-07-20 10:00:00 2006-07-20 10:05:00 2006-07-20 10:10:00 2006-07-20 10:15:00 2006-07-20 10:20:00 2006-07-20 10:25:00 2006-07-20 10:35:00 2006-07-20 10:45:00 (8 rows) select t, extract(minute from t) / 20, case floor((extract(minute from t) / 20)) when 0 then '00' when 1 then '20' when 2 then '40' end from times; t | ?column? | case ---------------------+----------+------ 2006-07-20 10:00:00 | 0 | 00 2006-07-20 10:05:00 | 0.25 | 00 2006-07-20 10:10:00 | 0.5 | 00 2006-07-20 10:15:00 | 0.75 | 00 2006-07-20 10:20:00 | 1 | 20 2006-07-20 10:25:00 | 1.25 | 20 2006-07-20 10:35:00 | 1.75 | 20 2006-07-20 10:45:00 | 2.25 | 40 (8 rows) HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
В списке pgsql-general по дате отправления: