TimeZones in SQL

Current time
SQL> select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Current Time"
2 from dual;

Current Time
------------------------
Tue 10-Feb-2009 00:40:27

[OR]

SQL> alter session set nls_date_format='dd-mon-yyyy HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
10-feb-2009 00:47:48

[OR]


SQL> select current_date from dual;

CURRENT_DATE
--------------------
10-feb-2009 00:48:52

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
----------------------------------------------------
-05:00

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
------------------------------------------------------
10-FEB-09 12.53.13.998000 AM -05:00

Above returns TimeStamp with Time ZOne

SQL> select localtimestamp from dual;

LOCALTIMESTAMP
------------------------------------------
10-FEB-09 12.54.29.396000 AM

Above returns only timestamp



SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
-----------------------------------------------
-05:00

SQL> select dbtimezone from dual;

DBTIME
------
+00:00


SQL> select hiredate from emp;

HIREDATE
--------------------
17-dec-1980 00:00:00
20-feb-1981 00:00:00
22-feb-1981 00:00:00
02-apr-1981 00:00:00
28-sep-1981 00:00:00
01-may-1981 00:00:00
09-jun-1981 00:00:00
19-apr-1987 00:00:00
17-nov-1981 00:00:00
08-sep-1981 00:00:00
23-may-1987 00:00:00

HIREDATE
--------------------
03-dec-1981 00:00:00
03-dec-1981 00:00:00
23-jan-1982 00:00:00

14 rows selected.


SQL> desc emp
Name Null? Type
----------------------------------------- -------- -----------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)


You can convert from DATE to TIMESTAMP when the column has data, but you cannot convert from DATE or TIMESTAMP to TIMESTAMP WITH TIME ZONE unless the column is empty.

You can specify the fractional seconds precision for timestamp. If none is specified, as in the above example, then it defaults to 6.

SQL> alter table emp modify hiredate timestamp(7);

Table altered.

SQL> select hiredate from emp;

HIREDATE
------------------------------------
17-DEC-80 12.00.00.0000000 AM
20-FEB-81 12.00.00.0000000 AM
22-FEB-81 12.00.00.0000000 AM
02-APR-81 12.00.00.0000000 AM
28-SEP-81 12.00.00.0000000 AM
01-MAY-81 12.00.00.0000000 AM
09-JUN-81 12.00.00.0000000 AM
19-APR-87 12.00.00.0000000 AM
17-NOV-81 12.00.00.0000000 AM
08-SEP-81 12.00.00.0000000 AM
23-MAY-87 12.00.00.0000000 AM

HIREDATE
------------------------------------
03-DEC-81 12.00.00.0000000 AM
03-DEC-81 12.00.00.0000000 AM
23-JAN-82 12.00.00.0000000 AM

14 rows selected.

EXTRACT function
------------
SELECT EXTRACT ([YEAR] [MONTH][DAY] [HOUR] [MINUTE][SECOND]
[TIMEZONE_HOUR] [TIMEZONE_MINUTE]
[TIMEZONE_REGION] [TIMEZONE_ABBR]
FROM [datetime_value_expression] [interval_value_expression]);


SQL> select extract(year from sysdate) from dual;

EXTRACT(YEARFROMSYSDATE)
------------------------
2009

SQL> select extract(timezone_region from current_timestamp) from dual;

EXTRACT(TIMEZONE_REGIONFROMCURRENT_TIMESTAMP)
----------------------------------------------------------------
UNKNOWN

SQL> select extract(timezone_abbr from current_timestamp) from dual;

EXTRACT(TI
----------
UNK

TZ_OFFSET function:
--------------------------
returns the time zone offset. For example, if the function returns -05:00, it indicates that the time zone where the command was executed is five hours behind UTC (Coordinated Universal Time).


SQL> select tz_offset(sessiontimezone) from dual;

TZ_OFFS
-------
-05:00

SQL> select tz_offset(dbtimezone) from dual;

TZ_OFFS
-------
+00:00

Query v$timezone_names to get valid time zone name values:
SQL> select * from v$timezone_names;


FROM_TZ function: Converts a TIMESTAMP value to TIMESTAMP WITH TIME ZONE value
----------------------------

SQL> select from_tz(timestamp '2008-03-20 10:00:00', 'US/Pacific') from dual;

FROM_TZ(TIMESTAMP'2008-03-2010:00:00','US/PACIFIC')
---------------------------------------------------------------------------
20-MAR-08 10.00.00.000000000 AM US/PACIFIC


SQL> select from_tz(timestamp '2008-03-20 10:00:00', '-05:00') from dual;

FROM_TZ(TIMESTAMP'2008-03-2010:00:00','-05:00')
---------------------------------------------------------------------------
20-MAR-08 10.00.00.000000000 AM -05:00

TO_TIMESTAMP
---------------
SQL> select to_timestamp('05-12-09 13:20:00', 'MM-DD-YY HH24:MI:SS') from dual;

TO_TIMESTAMP('05-12-0913:20:00','MM-DD-YYHH24:MI:SS')
---------------------------------------------------------------------------
12-MAY-09 01.20.00.000000000 PM


TO_TIMESTAMP_TZ
-------------------
SQL> select to_timestamp_tz('05-12-09 13:20:00 -5:00', 'MM-DD-YY HH24:MI:SS TZH:TZM') from dual;

TO_TIMESTAMP_TZ('05-12-0913:20:00-5:00','MM-DD-YYHH24:MI:SSTZH:TZM')
---------------------------------------------------------------------------
12-MAY-09 01.20.00.000000000 PM -05:00


TO_YMINTERVAL: Convert character string to an INTERVAL YEAR TO MONTH datatype.
-------------------
SQL> select hiredate from emp;

HIREDATE
-----------------------------------------
17-DEC-80 12.00.00.0000000 AM
20-FEB-81 12.00.00.0000000 AM
22-FEB-81 12.00.00.0000000 AM

SQL> select hiredate + to_yminterval('02-01') as new_hire_date from emp;

NEW_HIRE_DATE
-------------------------------------------------------------------------
17-JAN-83 12.00.00.000000000 AM
20-MAR-83 12.00.00.000000000 AM
22-MAR-83 12.00.00.000000000 AM

The character string can also have negative value. Belos, it returns a date that is one year and two months before the hire date.
SQL> select hiredate + to_yminterval('-01-02') as new_hire_date from emp;

NEW_HIRE_DATE
---------------------------------------------------------------------------
17-OCT-79 12.00.00.000000000 AM
20-DEC-79 12.00.00.000000000 AM
22-DEC-79 12.00.00.000000000 AM