Home » SQL & PL/SQL » SQL & PL/SQL » behaviour of to_timestamp and time zones (Win10, Oracle 12c)
behaviour of to_timestamp and time zones [message #652292] Mon, 06 June 2016 07:44 Go to next message
GusGF
Messages: 10
Registered: July 2015
Location: UK
Junior Member
orcl > select to_timestamp('2016-06-06 12:00', 'yyyy-mm-dd hh:mi') from dual;  
  
  
TO_TIMESTAMP('2016-06-0612:00','YYYY-MM-DDHH:MI')  
---------------------------------------------------------------------------  
06-JUN-16 12.00.00.000000000  
  
  
orcl > select to_timestamp('2016-06-06 12:00', 'yyyy-mm-dd hh:mi') at time zone '-05:00' from dual;  
  
  
TO_TIMESTAMP('2016-06-0612:00','YYYY-MM-DDHH:MI')ATTIMEZONE'-05:00'  
---------------------------------------------------------------------------  
06-JUN-16 06.00.00.000000000 -05:00  

I'd always assumed that 'to_timestamp' did not imply a time zone. From the second statement above it would seem to be doing that. I'd wrongly assumed the second statement above would instead output.

06-JUN-16 12.00.00.000000000 -05:00

In the second statement I'm applying a timezone offset to something that doesn't produce a time zone. Yet the output adjusts as if I'd supplied a time defaulted to the local time zone. How does it know what to offset from? Maybe this is academic as I probably wouldn't use a statement like this in reality but I was just curious if this was documented behaviour of the 'TO_TIMESTAMP' function or just something 'AT TIME ZONE' does?

[Updated on: Mon, 06 June 2016 07:46]

Report message to a moderator

Re: behaviour of to_timestamp and time zones [message #652294 is a reply to message #652292] Mon, 06 June 2016 08:12 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
It all depends on how you define the timestamp column. You can have it keep track of the timestamp zone or not. See the following link.

https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm
Re: behaviour of to_timestamp and time zones [message #652296 is a reply to message #652292] Mon, 06 June 2016 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

TO_TIMESTAMP produces a TIMESTAMP with no time zone information which means at your session time zone, when you add AT TIME ZONE, you convert the data to a TIMESTAMP WITH TIME ZONE.
See:
SQL> alter session set time_zone='GMT';

Session altered.

SQL> select to_timestamp('2016-06-06 12:00', 'yyyy-mm-dd hh:mi') from dual;
TO_TIMESTAMP('2016-06-0612:00','YYYY-MM-DDHH:MI')
---------------------------------------------------------------------------
06/06/2016 12:00:00.000

SQL>  select to_timestamp('2016-06-06 12:00', 'yyyy-mm-dd hh:mi') at time zone 'gmt' from dual;
TO_TIMESTAMP('2016-06-0612:00','YYYY-MM-DDHH:MI')ATTIMEZONE'GMT'
---------------------------------------------------------------------------
06/06/2016 12:00:00.000 GMT

SQL> select to_timestamp('2016-06-06 12:00', 'yyyy-mm-dd hh:mi') at time zone '-05:00' from dual;
TO_TIMESTAMP('2016-06-0612:00','YYYY-MM-DDHH:MI')ATTIMEZONE'-05:00'
---------------------------------------------------------------------------
06/06/2016 07:00:00.000 -05:00
The timestamp in my time zone is converted to a timestamp to the specified time zone by adding to it the time gap.

Re: behaviour of to_timestamp and time zones [message #652307 is a reply to message #652296] Mon, 06 June 2016 14:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
The following is just an additional demonstration that shows the creation of timestamp and timestamp with timezone when you use the statements to create a table, then describe the structure.

SCOTT@orcl_12.1.0.2.0> alter session set time_zone = 'GMT'
  2  /

Session altered.

SCOTT@orcl_12.1.0.2.0> create table test_tab as
  2  select to_timestamp('2016-06-06 12:00', 'yyyy-mm-dd hh:mi') col1,
  3  	    to_timestamp('2016-06-06 12:00', 'yyyy-mm-dd hh:mi') at time zone 'GMT' col2,
  4  	    to_timestamp('2016-06-06 12:00', 'yyyy-mm-dd hh:mi') at time zone '-05:00' col3
  5  from   dual
  6  /

Table created.

SCOTT@orcl_12.1.0.2.0> select * from test_tab
  2  /

COL1
---------------------------------------------------------------------------
COL2
---------------------------------------------------------------------------
COL3
---------------------------------------------------------------------------
06-JUN-16 12.00.00.000000000 PM
06-JUN-16 12.00.00.000000000 PM GMT
06-JUN-16 07.00.00.000000000 AM -05:00


1 row selected.

SCOTT@orcl_12.1.0.2.0> describe test_tab
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               TIMESTAMP(9)
 COL2                                               TIMESTAMP(9) WITH TIME ZONE
 COL3                                               TIMESTAMP(9) WITH TIME ZONE

SCOTT@orcl_12.1.0.2.0>

[Updated on: Mon, 06 June 2016 15:09]

Report message to a moderator

Re: behaviour of to_timestamp and time zones [message #652404 is a reply to message #652296] Wed, 08 June 2016 04:35 Go to previous messageGo to next message
GusGF
Messages: 10
Registered: July 2015
Location: UK
Junior Member
Thank you I was unsure about the behaviour due to an error in my notes.
Re: behaviour of to_timestamp and time zones [message #652405 is a reply to message #652307] Wed, 08 June 2016 04:47 Go to previous message
GusGF
Messages: 10
Registered: July 2015
Location: UK
Junior Member
Yes this is clearly the behaviour when no time zone is supplied. Thought they might have indicated this in the usage notes which is the clarification I was looking for.

orcl > select to_timestamp('2016-06-06 12:00', 'yyyy-mm-dd hh:mi') at time zone '-05:00' from dual;

TO_TIMESTAMP('2016-06-0612:00','YYYY-MM-DDHH:MI')ATTIMEZONE'-05:00'
---------------------------------------------------------------------------
06-JUN-16 06.00.00.000000000 -05:00


but I guess Oracle could equally have implemented it differently by saying well if no time zone argument is supplied and we don't assume the session time zone (+01:00) then we just append the time zone applied without changing the time as below.


orcl > select to_timestamp('2016-06-06 12:00', 'yyyy-mm-dd hh:mi') at time zone '-05:00' from dual;

TO_TIMESTAMP('2016-06-0612:00','YYYY-MM-DDHH:MI')ATTIMEZONE'-05:00'
---------------------------------------------------------------------------
06-JUN-16 12.00.00.000000000 -05:00
Previous Topic: Collections in plsql - syntax
Next Topic: Read/write files from remote server using utl_file in oracle
Goto Forum:
  


Current Time: Sun Aug 01 19:04:37 CDT 2021