Does HANA support timezone functions?

[Former Member]

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?

[Former Member]

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

Thanks

Dinesh

[lbreddemann]

Hi Dinesh,

the default, built-in list of timezones in SAP HANA is not documented.

Since timezone definitions are under constant change it is anyhow required to load updated definitions for proper use.

See my blog for links to the relevant SAP notes on how to maintain the timezone definitions.

- Lars

[tomas-krojzl]

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

[tomas-krojzl]

Hello,

however I am not sure if this is exactly what you were looking for...

Tomas

[former_member223694]

This message was moderated.

[former_member223694]

Hi

Is there a document which enlists set of standard timezone codes as recognized by this function?

I was trying to use 'IST' as timezonearg, for Indian Standard Time, but function localtoutc() throws timezone not found error.

Many thanks

K Sudhakaran

[Mofizur]

Hi Babu,

May be you have already got the answer from the experts.Just to add..

As per SAP Note# 2000003 - FAQ: SAP HANA

31. Does SAP HANA support timezones?
Yes, see SAP Note 1791342 for more details.

Thanks,

Mofizur

[Former Member]

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?

[dmitry_kuznetsov1]

got a solution to this by using daydate(now()) - it perfectly replaces sy-datum

[Former Member]

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

[tomas-krojzl]

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

[tomas-krojzl]

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

Of course if you need to convert time from timezone A to timezone B where both are different from UTC - then you logically need to use both of them... First to convert timezone A to UTC and then from UTC to timezone B.

utctolocal(localtoutc(datearg, timezoneA), timezoneB)

Note: I did not have time to test this myself... just what SAP documentation is offering.

Tomas

[Former Member]

Thanks for your input.

I am looking for a function that will display the timezone. For example,

12/13/2011 23:30 EDT

Would component function return the timezone?

thanks

[tomas-krojzl]

Hello,

as far as I know SAP HANA does not store timezone information inside time types... In case that you want to store this information then you will need separate column for this information.

I would suggest following model:

- first column to store time itself (regardless of time zone) <--- replicated from source

- second column to store the timezone information <--- replicated from source

- third column to store time in UTC <--- calculated column from first two columns

Then you can use first two columns to display information in local timezone and third column to display in UTC as well as for calculations (like time difference, etc.)

Tomas

[tomas-krojzl]

Hello,

...so ultimately the answer to your original question is probably following:

No - SAP HANA at the moment does not support time zones (all time types are without timezone information) with small exception of two timezone conversion functions. However you have very easy possibility to model the timezone capability yourself.

Tomas

[Former Member]

Thanks Tomas.

[Former Member]

Hello,

We are trying to derive the date from date time stamp field basically tring to get 6 hours differnce from time stamp field

(Derive CST time from GMT)

In Oracle we can achieve as below this works from any date time stamp field.

SELECT Sysdate ,sysdate - (6/24) FROM dual

Please let me know if this can be achieved in HANA.

Thanks!

[lbreddemann]

Hello Ravi,

of course you can!

Since table DUAL doesn't exist in HANA, we'll have to stick to table DUMMY here

create table timedata (mydate timestamp);

insert into timedata values (current_timestamp);

select * from timedata;
MYDATE                 
03.03.2012 21:34:16.178

select mydate, add_days (mydate, -1) yesterday from timedata;

MYDATE                 ;YESTERDAY              
03.03.2012 21:34:16.178;02.03.2012 21:34:16.178

Ok, we don't seem to need DUMMY/DUAL at all, but I guess you get the idea....

Please do also check the SQL reference documentation available on http://help.sap.com/hana_appliance.

regards,

Lars

[Former Member]

Thanks for your response Lars, I am stil trying to find function which canactually be be used to remove 6 hours from the date time stamp. Find below example.

Mydate Mydate-6

3/2/2012 01:08:37 AM 3/1/2012 7:08:37 PM

Thanks in advance for your response.

[Former Member]

Hi Ravi,

The following function on 'dummy' table will work for you:

SELECT ADD_SECONDS (current_timestamp, -66060) "New Time" FROM DUMMY;

The 6 hours had to be expressed in seconds though...

Regards, Rahul

[Former Member]

Thanks Rahul & Lars!

[former_member201760]

Hi Lars and hi Rahul,

I have my timestamp in UTC. I am using a modelled calc view where i need to determine the elapsed time.

Here i am only having a n option to use the now() functionality to determine the elapsed time since current_timestamp is not exposed in the modelled calc view editor.

now() returns local timestamp however my source system and HANA are in 2 different timezone...

So how can i calculate the elapsed time?

BR,

Ramana

[Former Member]

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