Skip to Content
avatar image
Former Member

Problem with "summer" time (DST)

We have problem with datetime fields, when we work with HXE using ngdbc.jar (in eclipse or in own application).

Problem is in hours, when summer time is starting/ending.

For example this select shown different data, than are entered:

select to_seconddate('2017-03-26 01:00:00'), to_seconddate('2017-03-26 02:00:00'), to_seconddate('2017-10-29 01:00:00') from dummy;

Bad result: 26.03.2017 00:00:00, 26.03.2017 01:00:00, 29.10.2017 02:00:00

We don't know, how we can turn off this behaviour.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Feb 06 at 03:05 AM

    This behaviour is correct as - with DST active in your local time - 2017-03-26 02:00:00 simply is not a valid date-time.

    By definition of DST, 01:59:59.9 is the last "timestamp" before 03:00:00. Check e.g. here for details.

    Now you might argue that this is only true for timestamps that belong to a timezone that adheres to DST and you'd be correct. How does HANA "know" which timezone to assume in this case?

    That information is derived from two main sources:

    - the host-setting (i.e. what is set on Linux level) of the SAP HANA system

    - the user parameter 'TIME ZONE' (seems to be used for front-end only).

    So, for whatever you type into the SQL console, the currently assumed timezone is going to be the server setting.

    Run

    select * 
    from M_HOST_INFORMATION
    where key like 'time%';

    and see what comes out on your system.

    If it is set-up as recommended, you should see something like this:

    HOST   KEY            VALUE
    hxehosttimezone_offset 0   
    hxehosttimezone_name   UTC 

    With this setting your example for a "bad result" turns into a "good result":

    TO_SECONDDATE('2017-03-26 01:00:00')TO_SECONDDATE('2017-03-26 02:00:00')TO_SECONDDATE('2017-10-29 01:00:00')
    2017-03-26 01:00:00.0               2017-03-26 02:00:00.0               2017-10-29 01:00:00.0               
    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Now we have always correct results after setting java parameter -Duser.timezone=GMT.

      Note: It's fully independent on server's timezone.

      Thank you very much

      Jan