如何在 SQLite3 和 PostgreSQL 中从时间戳中选择日期

Question

在我的 Rails 项目中，我有一个充满时间戳行的分数表，我需要查询并查找每天的最新记录。尽管我在 sqlite3 中本地开发/测试，但我的生产数据库是 PostgreSQL。

我在构造 SQL 查询以仅从时间戳中提取日期时遇到问题。因此，如果这是时间戳：2010-12-13 23:03:40.485012+0000，那么我只需要检查 2010-12-13 并忽略时间。

Scores table:

id          :integer  
name        :string  
score       :integer  
updated_at  :timestamp  

由于每个名称每天都有多行，因此我想找到每天的最大时间戳，然后查看每个名称每天的最新分数。

我尝试过使用 DATE() 函数，但没有成功。有人知道这样做的最佳方法吗？

Answer 1

在SQLite 日期函数中，您需要一个冒号来分隔时区中的小时和分钟。

sqlite> select date('2010-12-13 23:03:40.485012+0000');
[NULL]
sqlite> select date('2010-12-13 23:03:40.485012+00:00');
2010-12-13

我的生产数据库是 PostgreSQL
虽然我在本地开发/测试
sqlite3。

这是一个坏主意。使用与生产相同的数据库进行测试。

Answer 2

对于 PostgreSQL，您可以使用：

CAST(updated_at AS DATE)

Answer 3

在 PostgreSQL 中，您可以使用 ::。

declare _now timestamp with time zont := now();
declare dt date := _now::date;