sql statement, now - 6 months in unix/epoch time

Question

my database is using an integer in epoch time for date in this table.

I want to do something like this:

select * from myTable where date_column > CURRENT_TIMESTAMP - 6 months

I'm not sure how to get 6 months out of this, dynamically. And the result of CURRENT_TIMESTAMP - 6 months would have to be in epoch time

insight appreciated

Answer 1

In Postgres, I believe the correct syntax is:

date_column > EXTRACT('epoch' from (NOW() - interval ' 6 months'))

or similarly:

to_timestamp(date_column) > (NOW() - interval ' 6 months'))

You can read the complete documentation of the date/time functions for Postgres for more information

Answer 2

In MSSQL you can use

select * 
from myTable 
where date_column > dateadd(month,-6,CURRENT_TIMESTAMP)

Answer 3

You can try this

SELECT *
  FROM myTable 
 WHERE TO_TIMESTAMP(date_column) > CURRENT_DATE - INTERVAL '6 MONTH';

Here is sqlfiddle