timestamp and calculations.

From: Thor Tall <tall_thor(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: timestamp and calculations.
Date: 2006-07-20 09:16:07
Message-ID: 20060720091607.66008.qmail@web50314.mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andreas Kretschmer 2006-07-20 09:57:45 Re: timestamp and calculations.
Previous Message surabhi.ahuja 2006-07-20 07:18:25 Re: psql seems to hang during delete query