StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>What you want is not possible, because timezones are not fixed, but change over time. Hence you cannot calculate THE difference between two timezones, but only the difference that is valid at a certain point in time.</p> <p>It is quite common, that timezones change over time. In the <a href="http://en.wikipedia.org/wiki/Tz_database">TZ Database</a> there are each year many changes for the time zones, e.g. places changing their time zone or changes on the start, end or difference of the day light saving time, or leap seconds getting inserted at "random" times.</p> <p>So to calculate the offset, you must also give a particular point in time, for which you want the offset calculated (i.e. which version of the time zone definitions should be applied).</p> <p>With this in mind, it becomes clear, how to calculate the difference between two time zones at a certain point in time:</p> <pre><code>select (TIMESTAMP '2012-06-30 22:00:00 US/Pacific') - (TIMESTAMP '2012-06-30 22:00:00 Europe/Berlin') as diff from dual ; DIFF ------------------------------ +000000000 09:00:00.000000000 </code></pre> <p>When you have this information, you have to decide, up to which precision you want the difference. I have chosen the date above on purpose, because on that night a leap second was inserted. So there might be some point in time, where you will get a difference of not exactly nine hours but of nine hours and one second (or is it nine hours minus one second).</p> <p>It is also important, that you insert the exact locations and not some other timestamp information. Locations can change to different time zones. Hence you have to specify tow locations and a point in time to get the correct time zone difference.</p> <p>If you want to round the difference, you can access its components with <code>EXTRACT(HOUR FROM ...)</code> and <code>EXTRACT(MINUTE FROM ...)</code>. If want to round at the second level, you can use the following trick:</p> <pre><code>select (to_number(to_char(sys_extract_utc(TIMESTAMP '2012-07-01 01:00:00 US/Pacific'), 'SSSSSFF3')) - to_number(to_char(sys_extract_utc(TIMESTAMP '2012-07-01 01:00:00 Europe/Berlin'), 'SSSSSFF3')))/1000 as diff_s from dual ; DIFF_S ---------- -54000 </code></pre>

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