Menu
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
631
You can use date_add function to convert Unix epoch to timestamp. This is simple and one of the easiest method. Following example uses date_add function to convert unix epoch to timestamp values. Note that, epoch type conversion using date_add is slow compared to other methods.
The Amazon Redshift implementation of EPOCH is relative to 1970-01-01 00:00:00.000000 independent of the time zone where the cluster resides. You might need to offset the results by the difference in hours depending on the time zone where the cluster is located.
Convert from human-readable date to epochlong epoch = new java.text.SimpleDateFormat("MM/dd/yyyy HH:mm:ss").parse("01/01/1970 01:00:00").getTime() / 1000; Timestamp in seconds, remove '/1000' for milliseconds. date +%s -d"Jan 1, 1980 00:00:01" Replace '-d' with '-ud' to input in GMT/UTC time.
Redshift doesn't have the from_unixtime() function. You'll need to use the below SQL query to get the timestamp. It just adds the number of seconds to epoch and return as timestamp.
select timestamp 'epoch' + your_timestamp_column * interval '1 second' AS your_column_alias from your_table If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
