Databases
StackOverflow2013
Postsdbo
POSpecify a time zone to use as the reference time zone
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSpecify a time zone to use as the reference time zone
    primarykey
    data
    text
    <p><em>Based on the first two answers, the question was unclear as originally posted, thus I am completely rewriting it:</em></p> <p>The following question is concerned only with how and what data is stored, and is no way shape or form concerned with converting data upon retrieval. As such, converting at SELECT to the desired time zone is not an appropriate answer.</p> <p>When inserting a value into a timestamp with time zone field, it is retrieved (and thus presumably stored) with the timestamp converted to the local time zone of the database at the time it was inserted.</p> <p>That is so say, a timestamp inserted as <code>2012-01-01 00:00:00+00:00</code> is retrieved as <code>2011-12-31 19:00:00-05</code>, where the local time zone of the database at the time of the insert was <code>-05</code>. Timestamps that were inserted during daylight savings time, when the database was at <code>-04</code>, are returned using the <code>-04</code> time zone.</p> <p>What I want is for all timestamps to use an arbitrary time zone when stored (and thus all be retrieved without any additional work as having that time zone). That is to say, were the server orbiting the planet, all times would be at <code>+00:00</code> (arbitrary time zone), instead of <code>-12:00</code> to <code>+12:00</code>.</p> <p>Can I insert into a timestamp with time zone column such that all timestamps are stored relative to an arbitrary time zone? If so, how?</p> <hr> <p>Original follows.</p> <p>When inserting a value into a <code>timestamp with time zone</code> field, it is being converted to the server's current time zone.</p> <p>Example: If I insert a value specifying a time zone of <code>-1</code>, retrieving it will give back the time at <code>-5</code> (the time zone of the server at the time it was inserted).</p> <p>Is it possible to specify that it should be stored using an arbitrary time zone?</p> <p>Note: This question is not how to convert the returned time to another time zone, this is specific to how the time is stored.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJonathan Leffler
    1. USMatt
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSpecify a time zone to use as the reference time zone
      1. USunutbu
      1. VTFavorite
    2. VO
      singulars
      1. POSpecify a time zone to use as the reference time zone
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSpecify a time zone to use as the reference time zone
      1. This table or related slice is empty.
      1. VTDownMod
    1. This table or related slice is empty.
 

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.

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