Function to convert epoch to timestamp in Amazon Redshift

1
votes

I'm working on Amazon Redshift database and I have dates in milliseconds since epoch. I want to convert that to timestamp, and writing this query that I found on another thread

SELECT TIMESTAMP 'epoch' + column_with_time_in_ms/1000 *INTERVAL '1 second'
FROM table_name LIMIT 1000;

gives me the result in YYYY-MM-DD HH:MM:SS.

My question is: How do I write a SQL function in Redshift that takes integer parameter that are the milliseconds and does this conversion?

Thanks.

1
sqldatetimetimestampamazon-redshiftsql-function

1 Answers

1
votes

You seem to want a scalar UDF that wraps the conversion code.

In Redshift, you could write this as:

create function ms_epoch_to_ts(int)
    returns timestamp
    immutable
as $$
    select timestamp 'epoch' + $1 / 1000 * interval '1 second'
$$ language sql;