CODEWITHSUNDEEP
sqlpostgresql
Panama Jack
Panama Jack

Reputation: 24478

SQL Get all records older than 30 days

Now I've found a lot of similar SO questions including an old one of mine, but what I'm trying to do is get any record older than 30 days but my table field is unix_timestamp. All other examples seem to use DateTime fields or something. Tried some and couldn't get them to work.

This definitely doesn't work below. Also I don't want a date between a between date, I want all records after 30 days from a unix timestamp stored in the database. I'm trying to prune inactive users.

simple examples.. doesn't work. 

SELECT * from profiles WHERE last_login < UNIX_TIMESTAMP(NOW(), INTERVAL 30 DAY)

And tried this

SELECT * from profiles WHERE UNIX_TIMESTAMP(last_login - INTERVAL 30 DAY)

Not too strong at complex date queries. Any help is appreciate.

Upvotes: 107

Views: 138883

Answers (4)

KPC
KPC

Reputation: 67

Using Postgres:

SELECT *
FROM profiles
WHERE
    to_timestamp(last_login) < NOW() - '30 days'::INTERVAL

Upvotes: 1

tanstaafl
tanstaafl

Reputation: 84

How about

SELECT * from profiles WHERE last_login < VALUEOFUNIXTIME30DAYSAGO

or

SELECT * from profiles WHERE last_login < (extract(epoch from now())-2592000)

Have a look at this post:

https://dba.stackexchange.com/questions/2796/how-do-i-get-the-current-unix-timestamp-from-postgresql

and this

http://www.epochconverter.com/

Upvotes: 4

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28591

Try something like:

SELECT * from profiles WHERE to_timestamp(last_login) < NOW() - INTERVAL '30 days'

Quote from the manual:

A single-argument to_timestamp function is also available; it accepts a double precision argument and converts from Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp with time zone. (Integer Unix epochs are implicitly cast to double precision.)

Upvotes: 161

Jan Spurny
Jan Spurny

Reputation: 5537

Unless I've missed something, this should be pretty easy:

SELECT * FROM profiles WHERE last_login < NOW() - INTERVAL '30 days';

Upvotes: 68

Related Questions