StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The <strong><em>proper</em></strong> way is not to use <code>time with time zone</code> (note the space between <code>time</code> and <code>zone</code>) at all, since it is broken by design. It is in the SQL standard, so Postgres supports the type - but advises <em>not</em> to use it. More in this related answer:<br> <a href="https://stackoverflow.com/questions/13239534/accounting-for-dst-in-postgres-when-selecting-scheduled-items/13243029#13243029">Accounting for DST in Postgres, when selecting scheduled items</a></p> <p>Since you are having problems with <a href="http://en.wikipedia.org/wiki/Daylight_saving_time" rel="nofollow noreferrer">DST</a>, <code>timetz</code> (short name) is a particularly bad choice. It is ill-equipped to deal with DST. It's impossible to tell whether <code>8:00:00</code> is in winter or summer time.</p> <p>Use <a href="http://www.postgresql.org/docs/current/interactive/datatype-datetime.html" rel="nofollow noreferrer"><code>timestamp with time zone</code> (<code>timstamptz</code>)</a> instead. You can always discard the date part. Simply use <code>start_time::time</code> to get the <em>local</em> time from a <code>timestamptz</code>. Or use <a href="http://www.postgresql.org/docs/current/interactive/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT" rel="nofollow noreferrer"><code>AT TIME ZONE</code></a> to transpose to your time zone.</p> <p>Generally, to <strong>take DST into account automatically</strong>, use a <strong><em>time zone name</em></strong> instead of a time zone abbreviation. More explanation in this related question & answer:<br> <a href="https://stackoverflow.com/questions/12042506/time-zone-names-with-identical-properties-yield-different-result-when-applied-to">Time zone names with identical properties yield different result when applied to timestamp</a></p> <p>In your particular case, you could probably use <code>America/Los_Angeles</code> (example with <code>timestamptz</code>):</p> <pre><code>INSERT INTO mytable(start_time, end_time) VALUES('1970-01-01 08:00:00 America/Los_Angeles' , '1970-01-01 18:00:00 America/Los_Angeles') </code></pre> <p>I found this by checking:</p> <pre><code>SELECT * FROM pg_timezone_names WHERE utc_offset = '-07:00' AND is_dst; </code></pre> <p>Basics about time zone handling:<br> <a href="https://stackoverflow.com/questions/9571392/ignoring-timezones-altogether-in-rails-and-postgresql/9576170#9576170">Ignoring timezones altogether in Rails and PostgreSQL</a></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