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
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
