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