How to get date and time separately in to two columns from a HANA unix timestamp field?

hana hana-sql-script
Hashim picture Hashim · Mar 21, 2017 · Viewed 8.1k times · Source

The unix timestamp value is coming to HANA via data services from MySQL DB.

I can however find the date using the following statement but wanted to know if there are any built in functions in SAP HANA or in Data Services Designer which can do this job? Also is there any way to get time from the result like we have TO_DATE for date other that RIGHT( )?

SELECT TO_VARCHAR (ADD_SECONDS( '1970-01-01 00:00:00', "1452261405"))

and it will return 2016-01-08 13:56:45

Answer

Christoph G picture Christoph G · Mar 21, 2017

You can use HANA built in functions TO_DATE and TO_TIME:

SELECT TO_TIMESTAMP (ADD_SECONDS( '1970-01-01 00:00:00', '1452261405')) TIMESTAMP,
       TO_DATE (ADD_SECONDS( '1970-01-01 00:00:00', '1452261405')) DATE,
       TO_TIME (ADD_SECONDS( '1970-01-01 00:00:00', '1452261405')) TIME
from PUBLIC.DUMMY

Gives you:

Fri Jan 08 2016 13:56:45 GMT+0000 (UTC) 08.01.2016 13:56:45

Related questions

HANA - Passing string variable into WHERE IN() clause in SQL script

Lets suppose I have some SQL script in a scripted calculation view that takes a single value input parameter and generates a string of multiple inputs for an input parameter in another calculation view. BEGIN declare paramStr clob; params = select …

Read More
How to convert an invalid number column to a number on HANA?

I have a table with a string column. I convert this column to a number using the function TO_INTEGER(). Ist work fine. But If I Aggregate the converted column with the function SUM I got this error: SAP DBTech …

Read More
Temporary tables in hana

it it possible to write script in hana that crate temporary table that is based on existing table (with no need to define columns and columns types hard coded ): create local temporary table #mytemp (id integer, name varchar(20)); create temporary …

Read More