pg.ddx.io pgsql-sql@postgresql.org mailing list archive help / color / mirror / Atom feed From: James G Wilkinson <jgw@alpinegeophysics.com>
To: pgsql-sql@postgresql.org
Subject: Date/Time Conversion
Date: Tue, 29 Mar 2005 16:04:47 -0800
Message-ID: <4249ED1F.6080909@alpinegeophysics.com> (raw )
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
view thread (5+ messages) latest in thread
Message-ID: <4249ED1F.6080909@alpinegeophysics.com>
Permalink: ../4249ED1F.6080909@alpinegeophysics.com/
Also on: postgresql.org/message-id/4249ED1F.6080909@alpinegeophysics.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jgw@alpinegeophysics.com
Subject: Re: Date/Time Conversion
In-Reply-To: <4249ED1F.6080909@alpinegeophysics.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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