CODEWITHSUNDEEP
postgresql
Vik G
Vik G

Reputation: 639

Last 7 days from postgres table epoch timestamp

I have a table (funny_quotes) which has a column called quote_date and it stores data in unix epoch time (seconds)

I want to run a query to only return data from this table(funny_quotes) only from the last 3 days.

I created a table locally in postgres but unable to store dates as epoch time, they only get stored as timestamp values

select * from funny_quotes where quote_date > (now() - interval '3 days')

Upvotes: 3

Views: 4037

Answers (1)

Nir Alfasi
Nir Alfasi

Reputation: 53525

You should modify the right side of the equation to epoch in order to be able to compare:

select * from funny_quotes where quote_date > extract(epoch from (now() - interval '3 days'))

Or the other way around: convert quote_date into timestamp:

select * from funny_quotes where to_timestamp(quote_date) > now() - interval '3 days'

You can read more about it in the docs

Upvotes: 4

Related Questions