now() AT TIME ZONE 'GMT'; 
Author Message
 now() AT TIME ZONE 'GMT';

Quote:

> If I want a column to have a default TIMESTAMP of 'now' but not in PST
> timezone but GMT,

It strikes me that you have a conceptual error.  Type TIMESTAMP (ie,
TIMESTAMP WITH TIME ZONE) *is* GMT internally; it is simply displayed
in whatever zone you've selected with SET TIMEZONE.  (This is basically
the same design as Unix system clocks --- always GMT --- and the TZ
environment variable.)  If you are trying to force it to a different
timezone then you are misusing it.

Type TIMESTAMP WITHOUT TIME ZONE doesn't have any concept of time zone
--- it's just a stated date and time with no particular zone reference.
If you apply the AT TIME ZONE operator to a TIMESTAMP WITH TIME ZONE
value, what happens is the internal GMT value is rotated to the
specified zone and then the output is labeled as type TIMESTAMP WITHOUT
TIME ZONE, preventing any further automatic zone rotations.  If you
coerce this back to TIMESTAMP WITH TIME ZONE, the implicitly assigned
zone is your local zone --- ie, your local zone is subtracted off again
to produce a supposed GMT value --- with entirely nonsensical results.

It wasn't clear to me exactly what you wanted to accomplish, but
applying AT TIME ZONE to something you are going to store in a TIMESTAMP
almost certainly isn't it.  My guess is that what you really want is
plain old unadorned "TIMESTAMP DEFAULT now()".

If you want to deliberately suppress time zone awareness, TIMESTAMP
WITHOUT TIME ZONE is the way to go.  If you want any awareness of zones,
you almost certainly want TIMESTAMP WITH TIME ZONE --- and just let the
system do what it wants to do, don't try to force some other approach.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Sat, 25 Sep 2004 10:53:20 GMT
 now() AT TIME ZONE 'GMT';

Quote:


> > If I want a column to have a default TIMESTAMP of 'now' but not in PST
> > timezone but GMT,

> It strikes me that you have a conceptual error.  Type TIMESTAMP (ie,

I guess so. So it is during the retrieval of a timestamp using jdbc, where
the timestamp
is converted into a java.sql.Timestamp that it gets converted to a local
timezone.
So I will have to use the jdbc access method that preserves (actually where
one can
set the desired timezone) the timezone.

Richard

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Sun, 26 Sep 2004 00:43:42 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. now() AT TIME ZONE 'GMT';

2. Time zones - convert local time to GMT

3. to_char(now(), 'YYYY') and time zones

4. CASE not working - I'm converting time zone data

5. Building multi time zones supported Db's

6. run-time error '3170': Couldn't fin installable ISAM

7. HELP converting a GMT to anyother Zone

8. **************!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Help me !!!!!!!!!!!!!!!!!!!!!!!!'''''''''''''''''''''''*************

9. Visual Basic's Programs Exchanging Zone

10. converting PST/PDT time to GMT time

11. How to Convert GMT(Greenwich) Time to short date and time and vice versa

12. GMT time to local server time


 
Powered by phpBB® Forum Software