Shikhar Gupta Shikhar Gupta - 11 months ago 83
SQL Question

How to convert bigint data into readable date format

I have a JIVE db on HANA platform. JIVEUSER table tracks lastloggedin information of all users on JIVE. The "lastloggedin" column is BIGINT data type.

I want to convert value of "lastloggedin" column into readable date format.

I created a query to convert BIGINT into date format and it works.

SELECT ADD_SECONDS (TO_TIMESTAMP ('1970-01-01 00:00:00'), 1340961977) "add seconds"

1340961977 - is the value in BIGINT and output is : Jun 29, 2012 9:26:17.0 AM

But when i run the same query on my table and its column i get an error. Please find below query and error message


SELECT ADD_SECONDS (TO_TIMESTAMP ('1970-01-01 00:00:00'), "lastloggedin") "add seconds"

[314]: numeric overflow: search table error: [6944] AttributeEngine: overflow in numeric calculation;longdate [here]add_seconds(longdate '1970-01-01 00:00:00.0000000', decfloat decfloat(fixed8_18 "lastloggedin")),lastloggedin = 1463427879839[fixed8_18.0]; checkNull false

Can anyone please help me with this error??

I am using SAP HANA Studio Version: 2.0.19

Answer Source

lastloggedin seems to be a Unix timestamp including milliseconds.

1463427879839 -> 2016-05-16 19:44:39.839

Try to divide by 1000, i don't know if ADD_SECONDS works on decimal values, too.

-- this might work
ADD_SECONDS (TO_TIMESTAMP ('1970-01-01 00:00:00'), "lastloggedin" / 1000.000)
-- or this
ADD_SECONDS (TO_TIMESTAMP ('1970-01-01 00:00:00'), "lastloggedin" / 1000)