| behaviour of to_timestamp and time zones [message #652292] |
Mon, 06 June 2016 07:44  |
 |
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 #652296 is a reply to message #652292] |
Mon, 06 June 2016 08:38  |
 |
Michel Cadot
Messages: 68641
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
|
|
|
|
| Re: behaviour of to_timestamp and time zones [message #652307 is a reply to message #652296] |
Mon, 06 June 2016 14:58 |
 |
Barbara Boehmer
Messages: 9088
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 #652405 is a reply to message #652307] |
Wed, 08 June 2016 04:47 |
|
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
