How to cast data in timestamp column to integer value in SQL query on postgres database?

Question

I am trying to read data from GP and ingest to HDFS using Spark. I need an integer column to partition the data which I read from the GP table. The problem here is I don't have a primary column or any column that has unique values. In this scenario, column that I can rely on the most is the timestamp column where I can convert it to Integer/Long .

The data in timestamp column is present in the format:

select max(last_updated_timestamp) from schema.tablename => 2018-12-13 13:29:55

Could anyone let me know how can I cast the timestamp column including its milliseconds and produce an EPOCH value from it which I can use it in my spark code ?

Georgi Raychev · Accepted Answer · 2019-02-19 13:46:28Z

1

You can use extract(epoch from last_updated_timestamp).

answered Feb 19, 2019 at 13:46

Georgi Raychev

1,3441 gold badge13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Metadata Over a year ago

Ok. Just asking, I am getting the values with decimal numbers: 1550551127.310969. Is it possible to get it as 1550551127310969 ?

Georgi Raychev Over a year ago

I think its ok for your needs just to multiply by 1000000: extract(epoch from last_updated_timestamp)*1000000 or even larger number if you get more digits after the decimal point.

Collectives™ on Stack Overflow

How to cast data in timestamp column to integer value in SQL query on postgres database?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related