hey guys i have a column in the database(postgresql) i want to insert the current time in GMT in this column when getting the current time and inserting it into the DB it's inserted in the server timezone GMT-5 although that time was in GMT+0 any ideas how to insert this time in the database in GMT timezone ?
A:
I think paragraph 8.5.1.2 of the manual might be enlightening. It states that by default time is assumed to be without timezone and if one is given it is silently ignored.
In order to make things clear I think it is best to explicitely cast the time :
pti=> select timestamp with time zone '20100610T180000-5';
timestamptz
------------------------
2010-06-11 01:00:00+02
(1 row)
pti=> select timestamp with time zone '20100610T180000PST';
timestamptz
------------------------
2010-06-11 04:00:00+02
(1 row)
As is evident the time with time zone is properly converted from localtime to server time.
Peter Tillemans
2010-06-09 20:49:19
I suppose you mean "paragraph 8.5.2. [Date/Time Output](http://www.postgresql.org/docs/8.4/static/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT)"?
Milen A. Radev
2010-06-09 21:02:02
No, I meant this : 8.5.1.2. TimesThe time-of-day types are time [ (p) ] without time zone and time [ (p) ] with time zone. time alone is equivalent to time without time zone.Valid input for these types consists of a time of day followed by an optional time zone. (See Table 8-11 and Table 8-12.) If a time zone is specified in the input for time without time zone, it is silently ignored.
Peter Tillemans
2010-06-09 21:20:30
then what should i do, i have a GMT timestamp and i want to insert it in the db with GMT to be in this form:'2004-10-19 10:23:54+00'instead of the server timezone '2004-10-19 10:23:54-5'
2010-06-09 21:25:10
@Peter - times and timestamps are completely different beasts. And your may want to fix that paragraph number if you think that's the relevant info.
Milen A. Radev
2010-06-09 22:03:58
Oops, now I see my mistake, thanks. Yes unfortunately they are very different.
Peter Tillemans
2010-06-09 22:21:04