StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
9576170
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
7
CommunityOwnedDate
CreationDate
2012-03-06T00:21:54.750
FavoriteCount
0
LastActivityDate
2018-04-22T01:52:44.053
LastEditDate
2018-04-22T01:52:44.053
LastEditorUserId
939860
OwnerUserId
939860
ParentId
9571392
PostTypeId
2
Score
299
ViewCount
0
LastEditorDisplayName
text
Body
The data type <a href="https://www.postgresql.org/docs/current/interactive/datatype-datetime.html" rel="noreferrer"><code>timestamp</code></a> is the short name for <code>timestamp without time zone</code>. The other option <a href="https://www.postgresql.org/docs/current/interactive/datatype-datetime.html" rel="noreferrer"><code>timestamptz</code></a> is short for <code>timestamp with time zone</code>. <code>timestamptz</code> is the preferred type in the date/time family, literally. It has <code>typispreferred</code> set in <a href="https://www.postgresql.org/docs/current/static/catalog-pg-type.html" rel="noreferrer"><code>pg_type</code></a>, which can be relevant: <ul> <li><a href="https://stackoverflow.com/questions/14113469/generating-time-series-between-two-dates-in-postgresql/46499873#46499873">Generating time series between two dates in PostgreSQL</a></li> </ul> <h3>Epoch</h3> Internally, timestamps are stored as a count from an <a href="https://en.wikipedia.org/wiki/Epoch_%28reference_date%29" rel="noreferrer">epoch</a>. Postgres uses an epoch of the first moment of the first day of the year 2000 in UTC, that is, 2000-01-01T00:00:00Z. Eight <a href="https://en.wikipedia.org/wiki/Octet_%28computing%29" rel="noreferrer">octets</a> are used to store the count number. Depending on a compile time option, that number is either: <ul> <li>An 8-byte integer (default), with 0 to 6 digits of a fractional second</li> <li><strike>A floating point number (deprecated), with 0 to 10 digits of a fractional second, where precision quickly degrades for values further away from epoch.</strike> Modern Postgres installations use the 8-byte integer.</li> </ul> Note that Postgres does not use <a href="https://en.wikipedia.org/wiki/Unix_time" rel="noreferrer">Unix time</a>. Postgres’ epoch is first moment of 2000-01-01 rather than Unix’ 1970-01-01. While Unix time has a resolution of whole seconds, Postgres keeps fractions of seconds. <h3><code>timestamp</code></h3> If you define a data type <code>timestamp</code> <code>[without time zone]</code> you are telling Postgres: "I am not providing a time zone explicitly, assume the current time zone instead. Postgres saves the timestamp as is - ignoring a time zone modifier if you should add one! When you later display that <code>timestamp</code>, you get back what you entered literally. With the same time zone setting all is fine. If the time zone setting for the session changes, so does the meaning of the <code>timestamp</code> - the value stays the same. <h3><code>timestamptz</code></h3> Handling of <code>timestamp with time zone</code> is subtly different. <a href="https://www.postgresql.org/docs/current/interactive/datatype-datetime.html#AEN5714" rel="noreferrer">I quote the manual here</a>: <blockquote> For <code>timestamp with time zone</code>, the internally stored value is always in UTC (Universal Coordinated Time ...) </blockquote> Bold emphasis mine. The time zone itself is never stored. It is an input modifier used to compute the according UTC timestamp, which is stored - or and output modifier used to compute the local time to display - with appended time zone offset. If you don't append an offset for <code>timestamptz</code> on input, the current time zone setting of the session is assumed. All computations are done with UTC timestamp values. If you have to (or may have to) deal with more than one time zone, use <code>timestamptz</code>. Clients like psql or pgAdmin or any application communicating via <a href="https://www.postgresql.org/docs/current/static/libpq.html" rel="noreferrer">libpq</a> (like Ruby with the pg gem) are presented with the timestamp plus offset for the current time zone or according to a requested time zone (see below). It is always the same point in time, only the display format varies. Or, <a href="https://www.postgresql.org/docs/current/interactive/datatype-datetime.html#DATATYPE-TIMEZONES" rel="noreferrer">as the manual puts it</a>: <blockquote> All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the <a href="https://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-TIMEZONE" rel="noreferrer">TimeZone</a> configuration parameter before being displayed to the client. </blockquote> Consider this simple example (in psql): <pre> db=# SELECT timestamptz '2012-03-05 20:00+03'; timestamptz ------------------------ 2012-03-05 18:00:00+01 </pre> Bold emphasis mine. What happened here? I chose an arbitrary time zone offset <code>+3</code> for the input literal. To Postgres, this is just one of many ways to input the UTC timestamp <code>2012-03-05 17:00:00</code>. The result of the query is displayed for the current time zone setting Vienna/Austria in my test, which has an offset <code>+1</code> during winter and <code>+2</code> during summer time: <code>2012-03-05 18:00:00+01</code>, because it falls into winter time. Postgres has already forgotten how this value has been entered. All it remembers is the value and the data type. Just like with a decimal number. <code>numeric '003.4'</code>, <code>numeric '3.40'</code> or <code>numeric '+3.4'</code> - all result in the exact same internal value. <h3><code>AT TIME ZONE</code></h3> As soon as you get a grasp on this logic, you can do anything you want. All that's missing now, is a tool to interpret or represent timestamp literals according to a specific time zone. That's where the <a href="https://www.postgresql.org/docs/current/interactive/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT" rel="noreferrer"><code>AT TIME ZONE</code></a> construct comes in. There are two different use cases. <code>timestamptz</code> is converted to <code>timestamp</code> and vice versa. To enter the UTC <code>timestamptz</code> <code>2012-03-05 17:00:00+0</code>: <pre><code>SELECT timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC' </code></pre> ... which is equivalent to: <pre><code>SELECT timestamptz '2012-03-05 17:00:00 UTC' </code></pre> To display the same point in time as EST <code>timestamp</code> (Eastern Standard Time): <pre><code>SELECT timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC' AT TIME ZONE 'EST' </code></pre> That's right, <code>AT TIME ZONE 'UTC'</code> twice. The first one interprets the <code>timestamp</code> value as (given) UTC timestamp returning the type <code>timestamptz</code>. The second one converts the <code>timestamptz</code> to the <code>timestamp</code> in the given time zone 'EST' - what a clock in the time zone EST displays at this unique point in time. <h3>Examples</h3> <pre><code>SELECT ts AT TIME ZONE 'UTC' FROM ( VALUES (1, timestamptz '2012-03-05 17:00:00+0') , (2, timestamptz '2012-03-05 18:00:00+1') , (3, timestamptz '2012-03-05 17:00:00 UTC') , (4, timestamp '2012-03-05 11:00:00' AT TIME ZONE '+6') , (5, timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC') , (6, timestamp '2012-03-05 07:00:00' AT TIME ZONE 'US/Hawaii') -- ① , (7, timestamptz '2012-03-05 07:00:00 US/Hawaii') -- ① , (8, timestamp '2012-03-05 07:00:00' AT TIME ZONE 'HST') -- ① <strike>, (9, timestamp '2012-03-05 18:00:00+1')</strike> -- ② loaded footgun! ) t(id, ts); </code></pre> Returns 8 (or 9) identical rows with a timestamptz columns holding the same UTC timestamp <code>2012-03-05 17:00:00</code>. The 9th row sort of happens to work in my time zone, but is an evil trap. See below. ① Rows 6 - 8 with time zone name and time zone abbreviation for Hawaii time are subject to DST (daylight saving time) and might differ, though not currently. A time zone name like <code>'US/Hawaii'</code> is aware of DST rules and all historic shifts automatically, while an abbreviation like <code>HST</code> is just a dumb code for a fixed offset. You may need to append a different abbreviation for summer / standard time. The name correctly interprets any timestamp at the given time zone. An abbreviation is cheap, but needs to be the right one for the given timestamp: <ul> <li><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></li> </ul> Daylight Saving Time is not among the brightest ideas humanity ever came up with. ② Row 9, marked as loaded footgun works for me, but only by coincidence. If you explicitly cast a literal to <code>timestamp [without time zone]</code>, <a href="https://www.postgresql.org/docs/current/interactive/datatype-datetime.html#AEN5700" rel="noreferrer">any time zone offset is ignored</a>! Only the bare timestamp is used. The value is then automatically coerced to <code>timestamptz</code> in the example to match the column type. For this step, the <code>timezone</code> setting of the current session is assumed, which happens to be the same time zone <code>+1</code> in my case (Europe/Vienna). But probably not in your case - which will result in a different value. In short: Don't cast <code>timestamptz</code> literals to <code>timestamp</code> or you lose the time zone offset. <h2>Your questions</h2> <blockquote> User stores a time, say March 17, 2012, 7pm. I don't want timezone conversions or the timezone to be stored. </blockquote> Time zone itself is never stored. Use one of the methods above to enter a UTC timestamp. <blockquote> I only use the users specified time zone to get records 'before' or 'after' the current time in the users local time zone. </blockquote> You can use one query for all clients in different time zones. For absolute global time: <pre><code>SELECT * FROM tbl WHERE time_col > (now() AT TIME ZONE 'UTC')::time </code></pre> For time according to the local clock: <pre><code>SELECT * FROM tbl WHERE time_col > now()::time </code></pre> Not tired of background information, yet? <a href="https://www.postgresql.org/docs/current/interactive/datatype-datetime.html" rel="noreferrer">There is more in the manual.</a>
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POIgnoring time zones altogether in Rails and PostgreSQL
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USErwin Brandstetter
UserOwnerUserId
1. USErwin Brandstetter
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. POIgnoring time zones altogether in Rails and PostgreSQL
 singulars
 PostTypePostTypeId
 PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
3. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId
1. COMinor detail, but I think timestamps are stored internally as the number of microseconds since 2000-01-01 - see [date/time datatype](http://www.postgresql.org/docs/9.2/static/datatype-datetime.html) section of the manual. My own inspections of the source seem to confirm it. Strange to use a different origin for the epoch!
 singulars
 PostPostId
 PO
 UserUserId
 USharmic

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.