13 Replies Latest reply: Sep 25, 2013 4:15 AM by dinesh somani RSS

Does HANA support timezone functions?

Babu Krishnasamy
Currently Being Moderated

We have a datetime stored in HANA tables. I have gone thro the SQL reference guides but did not find any functions that would give me the timezone or translate one timezone to another.

 

Does HANA support timezone functions?

  • Re: Does HANA support timezone functions?
    Tomas Krojzl
    Currently Being Moderated

    Hello,

     

    Does HANA support timezone functions?

    Yes - please see SAP HANA Database - SQL Script Guide

    https://service.sap.com/~sapidb/011000358700000604932011

    page 27 - chapter Relational Operators - operator CE_CALC

     

    Date Functions

    utctolocal

    interpret datearg (a date, without timezone) as utc and convert it to the timezone named by timezonearg (a string)

    iutctolocal(datearg, timezonearg)

    localtoutc

    convert the local datetime datearg to the timezone specified by the string timezonearg, return as a date

    localtoutc(datearg, timezonearg)

     

    Tomas

  • Re: Does HANA support timezone functions?
    Kondal Kolipaka
    Currently Being Moderated

    Yes, it supports time zone functions.

     

    Here is the complete list. These are the same functions which are available in Calculated Attributes, Calculated Measures and Calculated Columns in modeling environment.

     

     

    creating date values Date values may be created by conversion from string, like other types, or by specifying individual components:

     

    date('2011')

    date(2011)

    date(2011, 9, 15)

    daydate('2011-01-02')

    daydate(2011, 1, 2)

     

    using format strings Date values may be used together with format strings, as described elsewhere in the NewDb documentation (look for descriptions of the TO_DATE and TO_CHAR SQL functions)

     

    format(longdate('2011-06-09 20:20:13.1234567'), 'YYYY/MM/DD"T"HH24:MI:SS.FF7')

    longdate('2011/06/09T20:20:13.1234567', 'YYYY/MM/DD"T"HH24:MI:SS.FF7')

     

    utctolocal  utctolocal(datearg, timezonearg)

    interpret datearg (a date, without timezone) as utc and convert it to the timezone named by timezonearg (a string)

     

    localtoutc  localtoutc(datearg, timezonearg)

    convert the local datetime datearg to the timezone specified by the string timezonearg, return as a date

     

    weekday  weekday(date)

    return the weekday as an integer in the range 0..6, 0 is monday.

     

    now  now()

    return the current date and time (localtime of the server timezone) as date

     

    daysbetween  daysbetween(date1, date2)

    daysbetween(daydate1, daydate2)

    daysbetween(seconddate1, seconddate2)

    daysbetween(longdate1, longdate2)

    return the number of days (integer) between date1 and date2. The first version is an alternative to date2 - date1.

     

    Instead of rounding or checking for exactly 24 hours distance, this will truncate both date values to day precision and subtract the resulting day numbers, meaning that if arg2 is not the calendar day following arg1, daysbetween will return 1 regardless of the time components of arg1 and arg2.

     

    secondsbetween  secondsbetween(seconddate1, seconddate2)

    secondsbetween(longdate1, longdate2)

    return the number of seconds the first to the second arg, as a fixed point number. The returned value is positive if the first argument is less than the second. The return values are fixed18.0 in both cases (note that it may prove more useful to use fixed11.7 in case of longdate arguments).

     

    component  component(date, int)

    the int argument may be int the range 1..6, the values mean year, day, month, hour, minute, second, respectively. If a component is not set in the date, the component function will return a default value, 1 for the month or the day, 0 for other components. The component function may also be applied to longdate and time types.

     

    addseconds  addseconds(date, int)

    addseconds(seconddate, decfloat)

    addseconds(longdate, decfloat)

    Return a date plus a number of seconds. Fractional seconds will also be used in case of longdate. Null handling is (in opposition to the default done with adds) to return null if any argument is null.

     

    adddays  adddays(date, int)

    adddays(daydate, int)

    adddays(seconddate, int)

    adddays(longdate, int)

    Return a date plus a number of days. Null handling is (in opposition to the default done with adds) to return null if any argument is null.

     

     

    Regards,

    Kondal

  • Re: Does HANA support timezone functions?
    ravi kumar
    Currently Being Moderated

    Hello,

     

    Babu is looking for below conversion.

    Example if one of our date fields shows a value of 12/14/2011 02:30 GMT, then it should be converted  from GMT to EST, so the new date would really 12/13/2011 23:30 EDT

     

     

    Thanks & Regards,

    Ravi Sembeti

    • Re: Does HANA support timezone functions?
      Tomas Krojzl
      Currently Being Moderated

      Hello,

       

      Example if one of our date fields shows a value of 12/14/2011 02:30 GMT, then it should be converted  from GMT to EST, so the new date would really 12/13/2011 23:30 EDT

      Yes - please read again description of the functions I posted:

      Date Functions

       

      utctolocal

      interpret datearg (a date, without timezone) as utc and convert it to the timezone named by timezonearg (a string)

      iutctolocal(datearg, timezonearg)

       

      localtoutc

      convert the local datetime datearg to the timezone specified by the string timezonearg, return as a date

      localtoutc(datearg, timezonearg)

       

      These functions are supposed to do exactly what you are asking. However you cannot use them anywhere but only as part of CE_CALC operator in SQL Script (at least this is what I understand from SAP documentation).

       

      Tomas

  • Re: Does HANA support timezone functions?
    Mic xyz
    Currently Being Moderated

    How can I just get current date in calculated column?

     

    If I use now(), I get date and time, I don't want time. I just need current date to do calcuations but time is messing it up. In ECC is so easy, sy-datum.

     

    Any advice on how to do this?

  • Re: Does HANA support timezone functions?
    dinesh somani
    Currently Being Moderated

    Where can I get a list of all timezones recognized and their meaning and description. E.g., "PST" and its UTC offset.


    Thanks

    Dinesh

Actions