On Thu, 2006-07-20 at 11:57 +0200, Andreas Kretschmer wrote: > Thor Tall <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly