| Convert local time to GMT using off set [message #650983] |
Mon, 09 May 2016 07:03  |
 |
rao_teertum@yahoo.com
Messages: 3
Registered: May 2016
|
Junior Member |
|
|
Dear Oracle gurus
I have the requirement to convert local time to GMT
I have local time which is server time
I have offset.
I was trying to understand the following from online help but I ma not sure where I have plugin the offset
select sysdate,
sysdate+(substr(tz_offset(dbtimezone),1,1)||'1')*to_dsinterval('0'||substr(tz_offset( DBTIMEZONE ),2, 5)||':00') from dual;
Thanks in advance for your help
|
|
|
|
|
|
|
|
|
|
| Re: Convert local time to GMT using off set [message #651013 is a reply to message #650993] |
Mon, 09 May 2016 16:29  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
There are always various different ways to do things and code that you find on the web may or may not apply to your situation and may or may not be accurate.
If your server uses local time, then you can use the method that Michel showed you to calculate GMT time. You do not need an offset as shown below. You could substitute any timestamp value for systimestamp.
SCOTT@orcl_12.1.0.2.0> col local format a37
SCOTT@orcl_12.1.0.2.0> col gmt format a37
SCOTT@orcl_12.1.0.2.0> select systimestamp local,
2 systimestamp at time zone 'GMT' gmt
3 from dual;
LOCAL GMT
------------------------------------- -------------------------------------
09-MAY-16 02.16.18.148000 PM -07:00 09-MAY-16 09.16.18.148000 PM GMT
1 row selected.
If you want a date instead of a timestamp, then the following is another method. You could substitute any date value for sysdate.
SCOTT@orcl_12.1.0.2.0> select sysdate local,
2 sysdate - (substr (tz_offset (sessiontimezone), 1, 3) / 24) gmt
3 from dual;
LOCAL GMT
------------------------------------- -------------------------------------
09-MAY-2016 02:16:18 PM 09-MAY-2016 09:16:18 PM
1 row selected.
If you have an offset stored in a column in a table in the typical offset format, then you can use something like this if you like. You can substitute any data value for sysdate.
SCOTT@orcl_12.1.0.2.0> create table test_tab (offset varchar2(6))
2 /
Table created.
SCOTT@orcl_12.1.0.2.0> insert into test_tab (offset) select tz_offset (sessiontimezone) from dual
2 /
1 row created.
SCOTT@orcl_12.1.0.2.0> select * from test_tab
2 /
OFFSET
------
-07:00
1 row selected.
SCOTT@orcl_12.1.0.2.0> select sysdate local,
2 sysdate - (substr (offset, 1, 3) / 24) gmt
3 from test_tab
4 /
LOCAL GMT
------------------------------------- -------------------------------------
09-MAY-2016 02:16:18 PM 09-MAY-2016 09:16:18 PM
1 row selected.
If your offset is stored in some other format, then you need to adapt the code to that format. In the example below, the offset is stored as a numeric value representing the number of hours. You can substitute any date value for sysdate.
SCOTT@orcl_12.1.0.2.0> drop table test_tab
2 /
Table dropped.
SCOTT@orcl_12.1.0.2.0> create table test_tab (offset number)
2 /
Table created.
SCOTT@orcl_12.1.0.2.0> insert into test_tab (offset) select substr (tz_offset (sessiontimezone), 1, 3) from dual
2 /
1 row created.
SCOTT@orcl_12.1.0.2.0> select * from test_tab
2 /
OFFSET
----------
-7
1 row selected.
SCOTT@orcl_12.1.0.2.0> select sysdate local,
2 sysdate - (offset / 24) gmt
3 from test_tab
4 /
LOCAL GMT
------------------------------------- -------------------------------------
09-MAY-2016 02:16:18 PM 09-MAY-2016 09:16:18 PM
1 row selected.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
