StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>In PostgreSQL you don't have to worry about the odd time zone offsets. Internally, for a <code>timestamp with time zone</code> (<code>timestaptz</code> for short) only the count of seconds since '1970-01-01' in UTC time zone is stored.</p> <p>The same is true for <code>timestamp</code> in MySQL 5.0 and above:<br> <a href="https://stackoverflow.com/questions/409286/datetime-vs-timestamp/602038#602038">Should I use field 'datetime' or 'timestamp'?</a></p> <p>If you are only interested in the <code>date</code>, you may want to convert the column from <code>timestamptz</code> to <code>date</code>. In Postgres, when casting to <code>date</code>, the <code>local</code> time zone is applied. Try:</p> <pre><code>SELECT '2013-05-21 00:19:50+02'::timestamptz::date </code></pre> <p>The odd time zone offsets are due to historically diverse regimes like "mean solar time" before the early 20th century. We had a similar question recently, where I answered with an explanation:<br> <a href="https://stackoverflow.com/questions/16572455/postgresql-getting-strange-formated-timestamp-with-time-zone/16573603#16573603">PostgreSql: Getting strange-formated "timestamp with time zone"</a></p> <p>More explanation for the Postgres data types <code>timestamp</code> and <code>timestamptz</code>:<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