based on HANA rev. 70

World Time Zones Map

A question came up about timezone handling in SAP HANA.

As you might know SAP HANA offers functions to deal with local time as well as with UTC based time and provides the means to convert between those two.

But how does SAP HANA knows what time zones exist, what their offsets are and how get these information updated once the time zones change (and yes, they do change quite often as can be seen here: International Date Line – Wikipedia, the free encyclopedia)?

SAP HANA can draw these information from two sources:

1. Built in default values. These are hard coded, unfortunately undocumented values, that are used when no other information is available.

2. Time zone data stored in the TZ* tables in schema SYSTEM (and only there).

SAP note 1791342 – Time Zone Support in HANA explains this in more detail.


Concerning the current timezone: this is taken from the LINUX environment of the user that starts the indexserver if I’m not mistaken.

This setting, that has to be the same on every node in a scale out setup, can be reviewed in system table M_HOST_INFORMATION.


select * from M_HOST_INFORMATION where upper(KEY) like '%TIMEZONE%'

HOST  |KEY            |VALUE

ld9506|timezone_offset|3600

ld9506|timezone_name  |CET

There you go, now you know.

– Lars

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

  1. Mark Förster

    Hi Lars,

    it was a clever idea to add “based on HANA rev. 70” in the top of your blog. The beginning of the daylight savings time in the USA was much fun for HANA administrators. We get a very mixed picture where HANA gets the current time zone from. Looks like it depends on the actual revision, SAP HANA development keeps on changing the algorithm about where HANA gets the current time zone from.

    Regards,

    Mark

    (0) 
  2. G.T.P.F. Cheung

    Hi Lars,

    Good stuff. Do you know where I can find the list of timezones that are expected in the UTCTOLOCAL/LOCALTOUTC functions? Can’t seem to find these anywhere.

    Cheers,

    Glenn

    (0) 
    1. Lars Breddemann Post author

      Hi Glenn

      sorry, undocumented means, well, undocumented.

      So, there currently is no place you could check this list (unless you have access to the SAP HANA source code)

      I’ll try and see if I can put this forward as a documentation request.

      – Lars

      (0) 
  3. Deyu Xu

    UTCTOLOCAL function doesn’t real work.

    It only knows the standard timezone name but not DST timezone name, for example,

    it works fine with EST

    UTCTOLOCAL (TO_TIMESTAMP(‘2012-01-01 10:00:00’, ‘YYYY-MM-DD HH24:MI:SS’), ‘EST’)

    But it doesn’t know the EDT

    it won’t work, which is not good. if you get your timezone name from a java application.

    UTCTOLOCAL (TO_TIMESTAMP(‘2012-01-01 10:00:00’, ‘YYYY-MM-DD HH24:MI:SS’), ‘EDT’)

    BUT UTCTOLOCAL function does able to handle DST.

    For instance:

    UTCTOLOCAL (TO_TIMESTAMP(‘2012-01-01 10:00:00’, ‘YYYY-MM-DD HH24:MI:SS’), ‘EST’)

    It will show: 2012-01-01 05:00:00.0

    UTCTOLOCAL (TO_TIMESTAMP(‘2012-08-01 10:00:00’, ‘YYYY-MM-DD HH24:MI:SS’), ‘EST’)

    It will show: 2012-08-01 06:00:00.0

    I hope HANA can also handle DST timezone name in near future

    (0) 
    1. Lars Breddemann Post author

      Before making such claims, why not follow the mentioned SAP note and import the timezone and daylight saving definitions first?

      It should be obvious that this kind of information is not stable and if you want to use it in your application you will have to provision the uptodate information.

      As an SAP employee, please make use of the internal communitites and JAM groups to clarify your questions, instead of making false statements about our products.

      – Lars

      (0) 
  4. Madhavi P

    Hello Lars,

     

    Thanks for Blog.

    Can you Please share is there any activity that we need to perform as part of administration as part time zone change

     

    Thanks

    (0) 
    1. Lars Breddemann Post author

      Please put your question – with a little bit more context about what you mean by ‘time zone change’ – into a question in the Q&A area of SCN.

      (0) 

Leave a Reply