niXforums Forum Index
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   PreferencesPreferences   Log in to check your private messagesLog in to check your private messages   Log inLog in 
·  nixdoc.net ·  man pages ·  Linux HOWTOs ·  FreeBSD Tips ·  Forums
navigation Forum index » Databases » PostgreSQL
timestamp and calculations.
Post new topic   Reply to topic Page 1 of 1 [3 Posts] View previous topic :: View next topic
Author Message
Sven Willenberger
*nix forums beginner


Joined: 15 Mar 2005
Posts: 49

PostPosted: Thu Jul 20, 2006 2:29 pm    Post subject: Re: timestamp and calculations. Reply with quote
On Thu, 2006-07-20 at 11:57 +0200, Andreas Kretschmer wrote:
Quote:
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


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Back to top
Andreas Kretschmer
*nix forums beginner


Joined: 17 Aug 2005
Posts: 48

PostPosted: Thu Jul 20, 2006 9:57 am    Post subject: Re: timestamp and calculations. Reply with quote
Thor Tall <tall_thor@yahoo.com> schrieb:

Quote:
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°

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Back to top
Thor Tall
*nix forums beginner


Joined: 20 Jul 2006
Posts: 1

PostPosted: Thu Jul 20, 2006 9:16 am    Post subject: timestamp and calculations. Reply with quote
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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Back to top
Google
Back to top
Display posts from previous:   
Post new topic   Reply to topic Page 1 of 1 [3 Posts] View previous topic :: View next topic
The time now is Thu Dec 04, 2008 4:47 am | All times are GMT
navigation Forum index » Databases » PostgreSQL
Jump to:  

Similar Topics
Topic Author Forum Replies Last Post
No new posts Timestamp vs timestamptz Antimon PostgreSQL 8 Thu Jul 13, 2006 11:35 pm
No new posts Calculate timestamp 00:00:00 last sunday griemer PHP 9 Thu Jul 13, 2006 6:47 am
No new posts File timestamp passed by variable? Dave Farrance shell 6 Tue Jul 11, 2006 5:24 pm
No new posts Calculating NTP timestamp in C program bg_ie@yahoo.com C 0 Mon Jul 10, 2006 11:11 pm
No new posts automatic timestamp Nestor MySQL 4 Thu Jul 06, 2006 12:26 am

MPAA | ADHD Coach | Loans | Myspace Layouts | Loans
Copyright © 2004-2005 DeniX Solutions SRL
 
Other DeniX Solutions sites: Unix/Linux blog |  electronics forum |  medicine forum |  science forum | 
Privacy Policy


Powered by phpBB © 2001, 2005 phpBB Group

[ Time: 0.1751s ][ Queries: 20 (0.0875s) ][ GZIP on - Debug on ]