pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDate/Time Conversion
5+ messages / 4 participants
[nested] [flat]
* Date/Time Conversion
@ 2005-03-30 00:04 James G Wilkinson <jgw@alpinegeophysics.com>
0 siblings, 3 replies; 5+ messages in thread
From: James G Wilkinson @ 2005-03-30 00:04 UTC (permalink / raw)
To: pgsql-sql
I hope that this is some silly beginner's mistake. I have spent quite a
bit of time
reading the PostgreSQL documentation and cannot find my error. I have also
scanned the PostgreSQL archive and the web for help, but I have not
found anything
to get me over the hump (it is probably out there, I just cannot find it).
All I am trying to do is convert GMT dates and times to an arbitrary
time zone. Here is my small test database:
beta_jgw=# \d scenario1.time_test;
Table "scenario1.time_test"
Column | Type | Modifiers
--------------------+----------------------+-----------
gmt_hour | integer |
gmt_date | date |
local_year | integer |
local_month | integer |
local_day | integer |
local_hour | integer |
local_date | date |
beta_jgw=# select * from scenario1.time_test;
gmt_hour | gmt_date | local_year | local_month | local_day |
local_hour | local_date
----------+------------+------------+-------------+-----------+------------+------------
1 | 2002-07-06 | | |
| |
14 | 2002-07-06 | | |
| |
20 | 2002-07-06 | | |
| |
18 | 2002-07-06 | | |
| |
3 | 2002-07-06 | | |
| |
5 | 2002-07-06 | | |
| |
10 | 2002-07-06 | | |
| |
13 | 2002-07-06 | | |
| |
0 | 2002-07-06 | | |
| |
2 | 2002-07-06 | | |
| |
4 | 2002-07-06 | | |
| |
20 | 2002-07-06 | | |
| |
22 | 2002-07-06 | | |
| |
23 | 2002-07-06 | | |
| |
7 | 2002-07-06 | | |
| |
8 | 2002-07-06 | | |
| |
10 | 2002-07-06 | | |
| |
Here are some simple SQL statements that I used to populate the empty
attributes:
beta_jgw=# set time zone GMT;
beta_jgw=# update scenario1.time_test set local_hour = extract(hour from
to_timestamp(to_char(gmt_date,'YYYY-MM-DD')||'
'||to_char(gmt_hour,'99')||':00:00-00','YYYY-MM-DD HH24:MI:SS') at time
zone 'EST');
beta_jgw=# update scenario1.time_test set local_year = extract(year from
to_timestamp(to_char(gmt_date,'YYYY-MM-DD')||'
'||to_char(gmt_hour,'99')||':00:00-00','YYYY-MM-DD HH24:MI:SS') at time
zone 'EST');
beta_jgw=# update scenario1.time_test set local_month = extract(month
from to_timestamp(to_char(gmt_date,'YYYY-MM-DD')||'
'||to_char(gmt_hour,'99')||':00:00-00','YYYY-MM-DD HH24:MI:SS') at time
zone 'EST');
beta_jgw=# update scenario1.time_test set local_day = extract(day from
to_timestamp(to_char(gmt_date,'YYYY-MM-DD')||'
'||to_char(gmt_hour,'99')||':00:00-00','YYYY-MM-DD HH24:MI:SS') at time
zone 'EST');
beta_jgw=# update scenario1.time_test set local_date =
to_date(to_char(local_year,'9999')||'-'||to_char(local_month,'99')||'-'||to_char(local_day,'99'),'YYYY-MM-DD');
And here are the contents of the table after running the SQL commands:
beta_jgw=# select * from scenario1.time_test;
gmt_hour | gmt_date | local_year | local_month | local_day |
local_hour | local_date
----------+------------+------------+-------------+-----------+------------+------------
1 | 2002-07-06 | 2002 | 7 | 5 |
20 | 2169-08-30
14 | 2002-07-06 | 2002 | 7 | 6 |
9 | 2169-08-30
20 | 2002-07-06 | 2002 | 7 | 6 |
15 | 2169-08-30
18 | 2002-07-06 | 2002 | 7 | 6 |
13 | 2169-08-30
3 | 2002-07-06 | 2002 | 7 | 5 |
22 | 2169-08-30
5 | 2002-07-06 | 2002 | 7 | 6 |
0 | 2169-08-30
10 | 2002-07-06 | 2002 | 7 | 6 |
5 | 2169-08-30
13 | 2002-07-06 | 2002 | 7 | 6 |
8 | 2169-08-30
0 | 2002-07-06 | 2002 | 7 | 5 |
19 | 2169-08-30
2 | 2002-07-06 | 2002 | 7 | 5 |
21 | 2169-08-30
4 | 2002-07-06 | 2002 | 7 | 5 |
23 | 2169-08-30
20 | 2002-07-06 | 2002 | 7 | 6 |
15 | 2169-08-30
22 | 2002-07-06 | 2002 | 7 | 6 |
17 | 2169-08-30
23 | 2002-07-06 | 2002 | 7 | 6 |
18 | 2169-08-30
7 | 2002-07-06 | 2002 | 7 | 6 |
2 | 2169-08-30
8 | 2002-07-06 | 2002 | 7 | 6 |
3 | 2169-08-30
10 | 2002-07-06 | 2002 | 7 | 6 |
5 | 2169-08-30
Can someone please tell me what I have done incorrectly to generate
"local_date"?
Again, all I want to do is convert the "gmt_hour" and "gmt_date" to an
arbitrarily
defined time zone (in this example, I used EST).
Any help will be appreciated.
Regards,
Jim
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Date/Time Conversion
@ 2005-04-03 03:49 Yasir Malik <ymalik@cs.stevens.edu>
parent: James G Wilkinson <jgw@alpinegeophysics.com>
2 siblings, 0 replies; 5+ messages in thread
From: Yasir Malik @ 2005-04-03 03:49 UTC (permalink / raw)
To: pgsql-sql
> beta_jgw=# update scenario1.time_test set local_hour = extract(hour from
> to_timestamp(to_char(gmt_date,'YYYY-MM-DD')||'
> '||to_char(gmt_hour,'99')||':00:00-00','YYYY-MM-DD HH24:MI:SS') at time zone
> 'EST'); ^^^^^^^^
>
Wild guess, but shouldn't that be :00:00:00?
Regards,
Yasir
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Date/Time Conversion
@ 2005-04-03 03:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: James G Wilkinson <jgw@alpinegeophysics.com>
2 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2005-04-03 03:50 UTC (permalink / raw)
To: James G Wilkinson <jgw@alpinegeophysics.com>; +Cc: pgsql-sql
James G Wilkinson <jgw@alpinegeophysics.com> writes:
> beta_jgw=# update scenario1.time_test set local_day = extract(day from
> to_timestamp(to_char(gmt_date,'YYYY-MM-DD')||'
> '||to_char(gmt_hour,'99')||':00:00-00','YYYY-MM-DD HH24:MI:SS') at time
> zone 'EST');
That seems like a mighty ugly way to do it; have you considered some
kind of timestamp arithmetic instead? But anyway, I suspect your
problem is you need a few 'FM' format specs to keep the to_char
functions from introducing unwanted spaces. Look at the intermediate
results of your expressions.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Date/Time Conversion
@ 2005-04-03 13:45 Yasir Malik <ymalik@cs.stevens.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Yasir Malik @ 2005-04-03 13:45 UTC (permalink / raw)
To: pgsql-sql
> beta_jgw=# update scenario1.time_test set local_hour = extract(hour from
>to_timestamp(to_char(gmt_date,'YYYY-MM-DD')||'
> '||to_char(gmt_hour,'99')||':00:00-00','YYYY-MM-DD HH24:MI:SS') at time
>zone 'EST'); ^^^^^^^^
>
This sounds like a stupid answer, but shouldn't that be :00:00:00?
Regards,
Yasir
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Date/Time Conversion
@ 2005-04-03 20:35 Greg Stark <gsstark@mit.edu>
parent: James G Wilkinson <jgw@alpinegeophysics.com>
2 siblings, 0 replies; 5+ messages in thread
From: Greg Stark @ 2005-04-03 20:35 UTC (permalink / raw)
To: James G Wilkinson <jgw@alpinegeophysics.com>; +Cc: pgsql-sql
James G Wilkinson <jgw@alpinegeophysics.com> writes:
> I hope that this is some silly beginner's mistake. I have spent quite a bit of
> time
> reading the PostgreSQL documentation and cannot find my error. I have also
> scanned the PostgreSQL archive and the web for help, but I have not found
> anything
> to get me over the hump (it is probably out there, I just cannot find it).
>
> All I am trying to do is convert GMT dates and times to an arbitrary
> time zone. Here is my small test database:
I think the closest thing to what you're trying to do is:
slo=> set timezone = 'GMT';
SET
slo=> select '2001-01-01'::date::timestamptz at time zone 'EST' ;
timezone
---------------------
2000-12-31 19:00:00
(1 row)
Why are you not just storing a "timestamp with timezone" with the actual time
of the event, and a second column with the local time zone in which you can
choose to use to display the time?
--
greg
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-04-03 20:35 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-30 00:04 Date/Time Conversion James G Wilkinson <jgw@alpinegeophysics.com>
2005-04-03 03:49 ` Yasir Malik <ymalik@cs.stevens.edu>
2005-04-03 03:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-03 13:45 ` Yasir Malik <ymalik@cs.stevens.edu>
2005-04-03 20:35 ` Greg Stark <gsstark@mit.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox