SQL获取30天之前的所有记录 [英] SQL Get all records older than 30 days

问题描述

现在，我发现了很多类似的SO问题，包括我的一个老问题，但是我想做的是获取30天之前的任何记录，但是我的表字段是unix_timestamp。所有其他示例似乎都使用DateTime字段或其他内容。尝试了一些但无法使其正常工作。

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.

下面这绝对不起作用。另外，我不希望介于日期之间的日期，我希望从数据库中存储的unix时间戳起30天后的所有记录。
我正在尝试修剪不活跃的用户。

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)  

并尝试了此

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

在复杂的日期查询中不太强大。任何帮助表示赞赏。

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

推荐答案

尝试类似的操作：

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

手册中的报价：

也可以使用单参数to_timestamp函数；它接受双精度参数，并从Unix纪元（自1970-01-01 00：00：00 + 00开始的秒数）转换为带时区的时间戳。 （整数Unix纪元隐式转换为双精度。）

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

这篇关于SQL获取30天之前的所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！