Forum Stats

  • 3,741,235 Users
  • 2,248,398 Discussions
  • 7,861,694 Comments

Discussions

Convert GMT to Local time

884364
884364 Member Posts: 12
edited Aug 20, 2012 5:39PM in SQL & PL/SQL
Hi All,

I am having confusion in converting UTC to locatime. I have column that stores time in UTC but I want to get the local time when I will do query on it.

I am looking for something like when I do query "SELECT some_func(time_column) FROM myTable" will return me corresponding local time for each row. I don't know whether there is any built in function for this.

Is there any such function or any logic to get the desire result?

Any help is appreciated.

Thanks
Tagged:

Answers

  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    881361 wrote:
    Hi All,

    I am having confusion in converting UTC to locatime. I have column that stores time in UTC but I want to get the local time when I will do query on it.

    I am looking for something like when I do query "SELECT some_func(time_column) FROM myTable" will return me corresponding local time for each row. I don't know whether there is any built in function for this.

    Is there any such function or any logic to get the desire result?

    Any help is appreciated.

    Thanks
    post CREATE TABLE for this table
  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions222.htm#SQLRF06152 might help

    Regards

    Etbin
  • 884364
    884364 Member Posts: 12
    CREATE TABLE MYACTIONS (
    "ID" NUMBER(*,0) NOT NULL ENABLE,
    "ACTION_TYPE" CHAR(16 BYTE) NOT NULL ENABLE,
    "TIME" TIMESTAMP (6) NOT NULL ENABLE
    )
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    881361 wrote:
    CREATE TABLE MYACTIONS (
    "ID" NUMBER(*,0) NOT NULL ENABLE,
    "ACTION_TYPE" CHAR(16 BYTE) NOT NULL ENABLE,
    "TIME" TIMESTAMP (6) NOT NULL ENABLE
    )
    "TIME" is a keyword & should NOT be used as a column name.
    TIMESTAMP datatype does not know or care about any timezone details.
    Therefore you need to manually do the transformation from GMT to local TZ yourself.
  • Biju Das
    Biju Das Member Posts: 393
    Use FROM_TZ

    http://toolkit.rdbms-insight.com/tz.php

    Regards
    Biju
  • IckyIckyChiMoon
    IckyIckyChiMoon Member Posts: 129 Blue Ribbon
    edited Aug 20, 2012 3:55PM
    I find it easiest to use the "from_tz" along with the "at time zone" functions:
    WITH  sample_dates  as
      ( select  timestamp '2012-04-18 16:23:22'   as dts from dual union all
        select  timestamp '2011-11-29 03:59:01'          from dual union all
        select  timestamp '2011-08-04 22:30:05'          from dual union all
        select  timestamp '2012-01-01 00:00:00'          from dual
      )
    select  dts
         ,  from_tz( dts, 'UTC' ) at time zone 'US/Eastern'  as rslt
      from  sample_dates ;
    And the cool thing is.... it adjusts for daylight savings time (run the above & see). Notice how 2 of them have a 4 hour offset and the other 2 have a 5 hour offset.

    You can also do this with the DATE data-type, you just have to cast your date to timestamp first then cast the answer back to DATE.
  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    edited Aug 20, 2012 5:37PM
    Why reinvent the wheel ... Use the built-in functions like the previously mentioned FROM_TZ to address the issue? Also you can use:
    orabase> SELECT TZ_OFFSET('US/Eastern') FROM dual;
    
    TZ_OFFS
    -------
    -04:00
    demo here:
    http://www.morganslibrary.org/reference/timestamp.html#ttzo

    For a reasonably complete list of Oracle functions:
    http://www.morganslibrary.org/reference/builtin_functions.html
  • IckyIckyChiMoon
    IckyIckyChiMoon Member Posts: 129 Blue Ribbon
    How am I 'reinventing the wheel'? And, ... I did use the FROM_TZ function (along with the other little piece).

    How does your solution convert between UTC and local time? Doesn't it just give you the offset... for today? Doesn't the offset change as dates move in and out of daylight savings time?
  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    edited Aug 20, 2012 5:39PM
    I thought you wanted to write your own function which is reinventing the wheel: Unfortunately a very common activity and quite often unnecessary.
This discussion has been closed.