Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Get all records older than 30 days

Tags:

sql

postgresql

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.

like image 985
Panama Jack Avatar asked Aug 01 '13 15:08

Panama Jack


People also ask

How do I get 30 days old data in SQL?

SELECT * FROM product WHERE pdate >= DATEADD(day, -30, getdate()).

How do I get last 7 days record in SQL?

Here's the SQL query to get records from last 7 days in MySQL. In the above query we select those records where order_date falls after a past interval of 7 days. We use system function now() to get the latest datetime value, and INTERVAL clause to calculate a date 7 days in the past.

How can I get last 3 months data in SQL?

In SQL Server, you can use the DATEADD() function to get last 3 months (or n months) records.


1 Answers

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.)

like image 149
Ihor Romanchenko Avatar answered Oct 10 '22 00:10

Ihor Romanchenko