Re: timestamp and calculations.

On Thu, 2006-07-20 at 11:57 +0200, Andreas Kretschmer wrote:
> 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

Alternatively:

select lpad((floor((extract (minute from my_timestamp) / 20)) * 20)::text,2,'0')

Sven