user2924127 user2924127 - 3 months ago 12
SQL Question

Get the time the users in their timezone sumbitted

Our application uses CURRENT_TIMESTAMP to store an event when a user submitted the data. This is stored in a TIMESTAMP WITH LOCAL TIME ZONE type column. Our servers are in PST timezone, but I want to see what time one of our users in Australia submitted the data in Australian time? I am confused how to do this properly? When I query the database right now this column seems to show the date the row was submitted but in PST time for every row.

Something like this:

SELECT
datetime_submitted -- I want this to display the time this value was created in Australian time not PST
FROM my_table
WHERE user = 'AUSTRALIAN';

Answer

Use the AT TIME ZONE clause:

select systimestamp                                 as server_timestamp, 
       systimestamp at time zone 'Australia/Sydney' as australia_timestamp 
from   dual;

SERVER_TIMESTAMP                       AUSTRALIA_TIMESTAMP                  
-------------------------------------- --------------------------------------
12-AUG-16 04.12.23.789000000 PM -05:00 13-AUG-16 07.12.23.789000000 AM AUSTRALIA/SYDNEY 

Right now it is 4:12:23 PM on 12 August 2016 at my location (Central time, US); it is already tomorrow in Australia, as you can see from the example. (Reminds me the joke - don't worry about the end of the world coming today, it's already tomorrow in Australia!)

Comments