StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied! <p>First off, PostgreSQL’s time handling and arithmetic is fantastic and Option 3 is fine in the general case. It is, however, an incomplete view of time and timezones and can be supplemented:</p> <ol> <li>Store the name of a user’s time zone as a user preference (e.g. <code>America/Los_Angeles</code>, not <code>-0700</code>).</li> <li>Have user events/time data submitted local to their frame of reference (most likely an offset from UTC, such as <code>-0700</code>).</li> <li>In application, convert the time to <code>UTC</code> and stored using a <code>TIMESTAMP WITH TIME ZONE</code> column.</li> <li>Return time requests local to a user's time zone (i.e. convert from <code>UTC</code> to <code>America/Los_Angeles</code>).</li> <li>Set your database's <code>timezone</code> to <code>UTC</code>.</li> </ol> <p>This option doesn’t always work because it can be hard to get a user’s time zone and hence the hedge advice to use <code>TIMESTAMP WITH TIME ZONE</code> for lightweight applications. That said, let me explain some background aspects of this this Option 4 in more detail.</p> <p>Like Option 3, the reason for the <code>WITH TIME ZONE</code> is because the time at which something happened is an <strong>absolute</strong> moment in time. <code>WITHOUT TIME ZONE</code> yields a <strong>relative</strong> time zone. Don't ever, ever, ever mix absolute and relative TIMESTAMPs.</p> <p>From a programmatic and consistency perspective, ensure all calculations are made using UTC as the time zone. This isn’t a PostgreSQL requirement, but it helps when integrating with other programming languages or environments. Setting a <code>CHECK</code> on the column to make sure the write to the time stamp column has a time zone offset of <code>0</code> is a defensive position that prevents a few classes of bugs (e.g. a script dumps data to a file and something else sorts the time data using a lexical sort). Again, PostgreSQL doesn’t need this to do date calculations correctly or to convert between time zones (i.e. PostgreSQL is very adept at converting times between any two arbitrary time zones). To ensure data going in to the database is stored with an offset of zero:</p> <pre class="lang-sql prettyprint-override"><code>CREATE TABLE my_tbl ( my_timestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), CHECK(EXTRACT(TIMEZONE FROM my_timestamp) = '0') ); test=> SET timezone = 'America/Los_Angeles'; SET test=> INSERT INTO my_tbl (my_timestamp) VALUES (NOW()); ERROR: new row for relation "my_tbl" violates check constraint "my_tbl_my_timestamp_check" test=> SET timezone = 'UTC'; SET test=> INSERT INTO my_tbl (my_timestamp) VALUES (NOW()); INSERT 0 1 </code></pre> <p>It's not 100% perfect, but it provides a strong enough anti-footshooting measure that makes sure the data is already converted to UTC. There are lots of opinions on how to do this, but this seems to be the best in practice from my experience.</p> <p>Criticisms of database time zone handling is largely justified (there are plenty of databases that handle this with great incompetence), however PostgreSQL’s handling of timestamps and timezones is pretty awesome (despite a few "features" here and there). For example, one such feature:</p> <pre class="lang-sql prettyprint-override"><code>-- Make sure we're all working off of the same local time zone test=> SET timezone = 'America/Los_Angeles'; SET test=> SELECT NOW(); now ------------------------------- 2011-05-27 15:47:58.138995-07 (1 row) test=> SELECT NOW() AT TIME ZONE 'UTC'; timezone ---------------------------- 2011-05-27 22:48:02.235541 (1 row) </code></pre> <p>Note that <code>AT TIME ZONE 'UTC'</code> strips time zone info and creates a relative <code>TIMESTAMP WITHOUT TIME ZONE</code> using your target’s frame of reference (<code>UTC</code>).</p> <p>When converting from an incomplete <code>TIMESTAMP WITHOUT TIME ZONE</code> to a <code>TIMESTAMP WITH TIME ZONE</code>, the missing time zone is inherited from your connection:</p> <pre class="lang-sql prettyprint-override"><code>test=> SET timezone = 'America/Los_Angeles'; SET test=> SELECT EXTRACT(TIMEZONE_HOUR FROM NOW()); date_part ----------- -7 (1 row) test=> SELECT EXTRACT(TIMEZONE_HOUR FROM TIMESTAMP WITH TIME ZONE '2011-05-27 22:48:02.235541'); date_part ----------- -7 (1 row) -- Now change to UTC test=> SET timezone = 'UTC'; SET -- Create an absolute time with timezone offset: test=> SELECT NOW(); now ------------------------------- 2011-05-27 22:48:40.540119+00 (1 row) -- Creates a relative time in a given frame of reference (i.e. no offset) test=> SELECT NOW() AT TIME ZONE 'UTC'; timezone ---------------------------- 2011-05-27 22:48:49.444446 (1 row) test=> SELECT EXTRACT(TIMEZONE_HOUR FROM NOW()); date_part ----------- 0 (1 row) test=> SELECT EXTRACT(TIMEZONE_HOUR FROM TIMESTAMP WITH TIME ZONE '2011-05-27 22:48:02.235541'); date_part ----------- 0 (1 row) </code></pre> <p>The bottom line:</p> <ul> <li>store a user’s time zone as a named label (e.g. <code>America/Los_Angeles</code>) and not an offset from UTC (e.g. <code>-0700</code>)</li> <li>use UTC for everything unless there is a compelling reason to store a non-zero offset</li> <li>treat all non-zero UTC times as an input error</li> <li>never mix and match relative and absolute timestamps</li> <li>also use <code>UTC</code> as the <code>timezone</code> in the database if possible</li> </ul> <p>Random programming language note: Python's <a href="http://docs.python.org/2/library/datetime.html"><code>datetime</code></a> data type is very good at maintaining the distinction between absolute vs relative times (albeit frustrating at first until you supplement it with a library like <a href="https://pypi.python.org/pypi/pytz">PyTZ</a>).</p> <hr> <p><strong>EDIT</strong></p> <p>Let me explain the difference between relative vs absolute a bit more.</p> <p>Absolute time is used to record an event. Examples: "User 123 logged in" or "a graduation ceremonies start at 2011-05-28 2pm PST." Regardless of your local time zone, if you could teleport to where the event occurred, you could witness the event happening. Most time data in a database is absolute (and therefore should be <code>TIMESTAMP WITH TIME ZONE</code>, ideally with a +0 offset and a textual label representing the rules governing the particular timezone - not an offset).</p> <p>A relative event would be to record or schedule the time of something from the perspective of a yet-to-be-determined time zone. Examples: "our business's doors open at 8am and close at 9pm", "let's meet every Monday at 7am for a weekly breakfast meeting," or "every Halloween at 8pm." In general, relative time is used in a template or factory for events, and absolute time is used for almost everything else. There is one rare exception that’s worth pointing out which should illustrate the value of relative times. For future events that are far enough in the future where there could be uncertainty about the absolute time at which something could occur, use a relative timestamp. Here’s a real world example:</p> <p>Suppose it’s the year 2004 and you need to schedule a delivery on October 31st in 2008 at 1pm on the West Coast of the US (i.e. <code>America/Los_Angeles</code>/<code>PST8PDT</code>). If you stored that using absolute time using <code>’2008-10-31 21:00:00.000000+00’::TIMESTAMP WITH TIME ZONE</code> , the delivery would have shown up at 2pm because the US Government passed the <a href="https://en.wikipedia.org/wiki/Energy_Policy_Act_of_2005#Change_to_daylight_saving_time">Energy Policy Act of 2005</a> that changed the rules governing daylight savings time. In 2004 when the delivery was scheduled, the date <code>10-31-2008</code> would have been Pacific Standard Time (<code>+8000</code>), but starting in year 2005+ timezone databases recognized that <code>10-31-2008</code> would have been Pacific Daylight Savings time (<code>+0700</code>). Storing a relative timestamp with the time zone would have resulted in a correct delivery schedule because a relative timestamp is immune to Congress’ ill-informed tampering. Where the cutoff between using relative vs absolute times for scheduling things is, is a fuzzy line, but my rule of thumb is that scheduling for anything in the future further than 3-6mo should make use of relative timestamps (scheduled = absolute vs planned = relative ???).</p> <p>The other/last type of relative time is the <code>INTERVAL</code>. Example: "the session will time out 20 minutes after a user logs in". An <code>INTERVAL</code> can be used correctly with either absolute timestamps (<code>TIMESTAMP WITH TIME ZONE</code>) or relative timestamps (<code>TIMESTAMP WITHOUT TIME ZONE</code>). It is equally correct to say, "a user session expires 20min after a successful login (login_utc + session_duration)" or "our morning breakfast meeting can only last 60 minutes (recurring_start_time + meeting_length)".</p> <p>Last bits of confusion: <code>DATE</code>, <code>TIME</code>, <code>TIME WITHOUT TIME ZONE</code> and <code>TIME WITH TIME ZONE</code> are all relative data types. For example: <code>'2011-05-28'::DATE</code> represents a relative date since you have no time zone information which could be used to identify midnight. Similarly, <code>'23:23:59'::TIME</code> is relative because you don't know either the time zone or the <code>DATE</code> represented by the time. Even with <code>'23:59:59-07'::TIME WITH TIME ZONE</code>, you don't know what the <code>DATE</code> would be. And lastly, <code>DATE</code> with a time zone is not in fact a <code>DATE</code>, it is a <code>TIMESTAMP WITH TIME ZONE</code>:</p> <pre class="lang-sql prettyprint-override"><code>test=> SET timezone = 'America/Los_Angeles'; SET test=> SELECT '2011-05-11'::DATE AT TIME ZONE 'UTC'; timezone --------------------- 2011-05-11 07:00:00 (1 row) test=> SET timezone = 'UTC'; SET test=> SELECT '2011-05-11'::DATE AT TIME ZONE 'UTC'; timezone --------------------- 2011-05-11 00:00:00 (1 row) </code></pre> <p>Putting dates and time zones in databases is a good thing, but it is <em>easy to get subtly incorrect results.</em> Minimal additional effort is required to store time information correctly and completely, however that doesn’t mean the extra effort is always required.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload