How to convert epoch to datetime redshift?

datetime amazon-redshift epoch
khusnanadia picture khusnanadia · Oct 2, 2016 · Viewed 49.4k times · Source

I work in dbeaver. I have a table x.

TABLE x has a column "timestamp" 

1464800406459 
1464800400452 
1464800414056 
1464800422854 
1464800411797

The result I want:

Wed, 01 Jun 2016 17:00:06.459 GMT
Wed, 01 Jun 2016 17:00:00.452 GMT
Wed, 01 Jun 2016 17:00:14.056 GMT
Wed, 01 Jun 2016 17:00:22.854 GMT 
Wed, 01 Jun 2016 17:00:11.797 GMT

I tried redshift query 

SELECT FROM_UNIXTIME(x.timestamp) as x_date_time 
FROM x

but didn't work.

Error occurred:

Invalid operation: function from_unixtime(character varying) does not exist

I also tried 

SELECT DATE_FORMAT(x.timestamp, '%d/%m/%Y') as x_date 
FROM x

Error occurred:

Invalid operation: function date_format(character varying, "unknown") does not exist

Is there any wrong with the syntax? Or is there another way to convert to human readable date and time?

Thanks in advance

Answer

s7vr picture s7vr · Oct 2, 2016

Redshift doesnt have the from_unixtime() function. You'll need to use the below sql query to get the timestamp. It just adds the no of seconds to epoch and return as timestamp.

select timestamp 'epoch' + your_timestamp_column * interval '1 second' AS your_column_alias
from your_table

Related questions

How to get the current time in Python

What is the module/method used to get the current time?

Read More
Converting string into datetime

I've got a huge list of date-times like this as strings: Jun 1 2005 1:33PM Aug 28 1999 12:00AM I'm going to be shoving these back into proper datetime fields in a database so I need to magic them into real datetime objects. This …

Read More
How do you get a timestamp in JavaScript?

How can I get a timestamp in JavaScript? Something similar to Unix timestamp, that is, a single number that represents the current time and date. Either as a number or a string.

Read More