Re: timestamp and calculations.

Thor Tall <tall_thor(at)yahoo(dot)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°