Re: timestamp and calculations.

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°