X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7B20A53B1B for ; Wed, 30 Mar 2005 01:05:06 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 40815-02 for ; Wed, 30 Mar 2005 00:04:56 +0000 (GMT) Received: from alpinegeophysics.com (sta-206-168-222-6.rockynet.com [206.168.222.6]) by svr1.postgresql.org (Postfix) with ESMTP id 9C9D453B07 for ; Wed, 30 Mar 2005 01:04:54 +0100 (BST) Received: from [192.168.1.100] [71.34.251.148] by alpinegeophysics.com with ESMTP (SMTPD32-7.07) id AC58CC004A; Tue, 29 Mar 2005 17:01:28 -0700 Message-ID: <4249ED1F.6080909@alpinegeophysics.com> Date: Tue, 29 Mar 2005 16:04:47 -0800 From: James G Wilkinson User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.2) Gecko/20040804 Netscape/7.2 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Date/Time Conversion Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200504/18 X-Sequence-Number: 20911 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