How to get the same date and time regardless of time zone

Question

How to save my "dates + times" in a PostgreSQL database?

This is what I want to achieve:

• How can I get all the records that happened (for example) on January 1, 2012 00:00:00 local time anywhere in the world?
• Display all records sorted by date according to UTC time. (2012 New Year in New York is later than New Year in London).

How to store data? I read that PostgreSQL stores all the time in UTC internally ( PostgreSQL documentation ), so my user timezone is actually lost.

I think I should use a single column with the type "timestamp without timezone":

• Point 1 is simple.
• And with another column of type "String" I will store the time zone string (for example, America / New_York) but then, point 2 seems to be still hard to do.

I hope I get it.

Change a new idea: I think that preserving two timestamps: one without a time zone (1. ok) and one with a time zone (2. ok)

Answer 1

Yes, PostgreSQL stores all timestamps as UTC internally. For timestamp with time zone offset is used only to set the time to UTC, but is not saved explicitly.

I would not save a time zone line or even less time zone abbreviations (they are inaccurate). Subsequently, this may require expensive calculations, because you need to consider daylight saving time and other oddities of the international time regime.

You can either save the time zone offset as an interval (takes 12 bytes ) or a number of seconds (takes 4 bytes as an integer), as I demonstrate in this related answer .

Or, as you suggested: keep a local timestamp in addition to the UTC timestamp (accepts 8 bytes ). This will simplify your tasks. Consider the following demo:

 -- DROP TABLE tbl; CREATE TEMP TABLE tbl (id int, ts_tz timestamp with time zone, ts timestamp); INSERT INTO tbl VALUES (1,'2012-1-1 00:00+01','2012-1-1 00:00+01') ,(2,'2012-1-1 00:00+02','2012-1-1 00:00+02') ,(3,'2012-1-1 00:01+03','2012-1-1 00:01+03') ,(4,'2012-1-1 00:02+04','2012-1-1 00:02+04'); 

Request for question 1:

 SELECT * FROM tbl WHERE ts = '2012-1-1 00:00'::timestamp; id | ts_tz | ts ----+------------------------+--------------------- 1 | 2012-01-01 00:00:00+01 | 2012-01-01 00:00:00 2 | 2011-12-31 23:00:00+01 | 2012-01-01 00:00:00 

Request for question 2:

 SELECT * FROM tbl ORDER BY ts_tz; id | ts_tz | ts ----+------------------------+--------------------- 4 | 2011-12-31 21:02:00+01 | 2012-01-01 00:02:00 3 | 2011-12-31 22:01:00+01 | 2012-01-01 00:01:00 2 | 2011-12-31 23:00:00+01 | 2012-01-01 00:00:00 1 | 2012-01-01 00:00:00+01 | 2012-01-01 00:00:00 

the tricky part with this solution may be to enter a local timestamp. This is easy if all data is entered locally. But it needs to be considered if you enter data, for example, in New York in Los Angeles. Use AT TIME ZONE construct for this:

 SELECT ('2012-1-1 00:00+00' AT TIME ZONE 'America/New_York')::timestamp , ('2012-1-1 00:00+00' AT TIME ZONE 'America/Los_Angeles')::timestamp timezone | timezone ---------------------+--------------------- 2011-12-31 19:00:00 | 2011-12-31 16:00:00 

Notice how I use a timestamp with a timezone as input. AT TIME ZONE gives different results for timestamps with or without a time zone.

Answer 2

I think you could save the date as a string and use ISO 8601 or RFC 2822 to save the time zone. Or, indeed, for more comparison options, the timestamp and timezone are stored in two different columns.