Postgres Pro
Downloads
Home   >   mailing lists

Thread: timestamp and calculations.

timestamp and calculations.

From
Thor Tall
Date:
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"

-- period_count = The number of periods we need to
calculate data for. (default 3)
-- start_time   = The time of the first period.
--   must be full hour '2005-10-24 02:00:00'
-- delta_time   = The length of each period in
minutes. (default 20)
------
select
  <start_time> + ((a.my_timestamp -
<start_time>)/<delta_time>) * <delta_time> as ts,
  ((a.my_timestamp - <start_time>)/<delta_time>) + 1
as Period
from tb_test as a
where
  a.my_timestamp >= <start_time> and a.ETI <
((<period_count> + <period_count> - 1) * <delta_time>
+ <start_time>)

My problem is that I cannot get the calculation to
work as I have a problem with the convertion between
timestamp and integer numbers. i.e. <delta_time> and
<period_count>.

Thanks,
Thor.


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Re: timestamp and calculations.

From
Andreas Kretschmer
Date:
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°

Re: timestamp and calculations.

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

Alternatively:

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

Sven