CODEWITHSUNDEEP
sqlamazon-redshiftepoch
Salvador Vigo
Salvador Vigo

Reputation: 457

How to extract year, month or day from BIGINT epoch timestamp when inserting in Redshift

I would like to extract a date format like month, year or day from a BIGINT timestamp 1543258003796 in the Redshift SQL environment when inserting data into a table.

I have a table like this:

CREATE TABLE time_table (
    month_date    character varying(20),
    year_date     character varying(20),
);

Now I want to populate the table time_table with data from another table ts_table that has a column with timestamp as BIGINT type:

INSERT INTO time_table (month_date, year_date)
SELECT EXTRACT(month from ts.timestamp) as month_date,
       EXTRACT(year from ts.timestamp)  as year_date
FROM ts_table ts;

It raises an error because ts.timestamp is a BIGINT. Should I first cast the BIGINT into something else? Or is there another function to perform this action? I tried several things but I am still not able to find a solution.

Upvotes: 1

Views: 1116

Answers (1)

Bill Weiner
Bill Weiner

Reputation: 11032

I assume that these BIGINT dates are epoch dates. So you first need to convert this to a timestamp - for example like so:

select timestamp 'epoch' + t.timestamp * interval '1 second' AS timest
from ts_table t;

Now this isn't want you want but it gets you into a timestamp data type and opens up the useful functions available to you.

Step 2 is to EXTRACT the year and month from this. Putting these together you get:

WITH ts_conv as (
  select timestamp 'epoch' + t.timestamp * interval '1 second' AS 
  timest
  from ts_table t
)
SELECT EXTRACT(month from ts.timest) as month_date,
       EXTRACT(year from ts.timest)  as year_date
FROM ts_conv ts;

And this of course can be inside your INSERT statement.

Upvotes: 2

Related Questions