0

Is there is any possibility in database, What ever time i am given which should store as GMT time stamp. If it so, Please share me.

Bathakarai
  • 1,517
  • 6
  • 23
  • 39

4 Answers4

3

For PostgreSQL, your table should have a column of type timestamp with time zone (timestamptz for short) like this:

CREATE TABLE tbl (id serial, ts timestamptz);

If what you want to store (let's call it my_ts) is ...

  • a local timestamp without time zone, i.e. it agrees with the current setting of timezone in your client:
    Insert as is, everything is saved as UTC automatically internally:

    INSERT INTO tbl (ts) VALUES my_ts'

  • a timestamp with time zone:
    Insert as is, everything is saved as UTC automatically internally:

    INSERT INTO tbl (ts) VALUES my_ts;

  • a timestamp without time zone but from another time zone:
    Say, you get timestamp without time zone values from the Olympic Games in London, but your client thinks you are at a different time zone. Transform the timestamp with the AT TIME ZONE construct:

    INSERT INTO tbl (ts) VALUES my_ts AT TIME ZONE 'Europe/London';

If you use the time zone name (instead of a time zone abbreviation, BST in this case), DST (daylight saving time) rules are applied automatically. Details in this related question.
More about timestamp handling in PostgreSQL in this related answer.

Now, if you want to display tbl.ts as UTC (~ GMT) timestamp without time zone, regardless of your current time zone, retrieve the values like this:

SELECT ts AT TIME ZONE 'UTC' FROM tbl;

Note that AT TIME ZONE has a different effect when applied to timestamptz than with timestamp! Read the manual carefully.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks for elaborating - might delete my answer, as you've covered it much more comprehensively. Times in DBs are complicated, ugh. – Craig Ringer Aug 22 '12 at 23:41
2

Times in timestamp with time zone (and the old-style timestamp, but you shouldn't generally use that) are always stored in UTC ("GMT") in PostgreSQL. They're converted to and from UTC for display. If you want them to be treated as UTC for input and output:

SET timezone = 'UTC'

in each session, or set the param at the database or user level. Your app must then send all timestamps qualified with a time zone if they're in a TZ other than UTC; you've told the system that "local time" is UTC.

The AT TIME ZONE construct can be useful if you want to explicitly convert timestamps to a time zone other than the default, eg:

regress=# SELECT TIMESTAMP '2012-01-01 00:00:00 UTC' AT TIME ZONE '+08:00';
        timezone        
------------------------
 2012-01-01 16:00:00+08
(1 row)
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
2

http://dev.mysql.com/doc/refman/5.5/en/datetime.html

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time.

Del Pedro
  • 1,216
  • 12
  • 32
0

The keyword you are looking for is UTC instead of GMT ("For most common purposes, UTC is synonymous with GMT, but GMT is no longer precisely defined by the scientific community.", wikipedia).

For MySQL, you can use the CONVERT_TZ() function (please note the warning in the manual about named time zones, it can break your queries if it's not properly set up).

Just a word of caution: there is no 100% reliable way to work with timezone conversion between MySQL and your application (see my blog post about mysql time zone conversions to learn in detail about why this is). It's better to pass your datetime strings to MySQL in UTC directly (e.g. by doing the conversion in the application itself). You almost always end up storing datetimes in a DATETIME field using UTC formatted date strings if you need reliability.

webdevelopersdiary
  • 253
  • 3
  • 9