postgres中的SQL将重复事件的日期时间转换为 future 的日期时间

Question

我仅使用日期时间来跟踪表中每周重复发生的事件。我只关心时间和它所在的星期几。

我需要能够将设置的 DATETIME 转换为当前或即将到来的 future 日期。

IE 如何使用当前日期将存储为 2013-02-22 12:00:00 的日期转换为下一次出现的日期？即下周五 12:00:00 或 2013-03-01 12:00:00，以便我可以按日期订购事件？

或者我可以将时间和星期几分别存储为数字 0-6。

更新:

从埃尔文那里我得到了类似的东西:

Event.order("date_trunc('week', now()::timestamp) + (start_at - date_trunc('week', start_at))")

这似乎是对它们进行排序，除了我得到的第一个日期是星期一，跳过了我知道周日存在的事件，它把它放在最后。

Answer 1

您的最佳选择是存储timestamp或timestamptz(timestamp with time zone)。如果您曾经或将来必须处理多个时区，请创建 timestamptz 并定义是否要使用本地时间或 UTC 或其他时间。此相关答案中的更多详细信息:
Ignoring timezones altogether in Rails and PostgreSQL

演示如何有效地将时间戳转置为当前星期(星期几和时间)。假设这里有时间戳:

SELECT date_trunc('week', now()::timestamp) + (t - date_trunc('week', t))
FROM (SELECT '2013-02-15 12:00:00'::timestamp AS t) x;

诀窍是计算相应周的开始时间与给定的时间戳之间的间隔，并在 date_trunc() 的帮助下将其添加到本周的开始时间.

ISO 周从星期一开始，星期日最后。

或者，仅向给定时间戳添加一周:

SELECT t + interval '1 week';

如果您只想ORDER BY，则只需要间隔:

ORDER BY (t - date_trunc('week', t))

如果您想将星期日放在第一位(轮换日期):

ORDER BY ((t + interval '1d') - date_trunc('week', (t + interval '1d'))

或更简单:

ORDER BY EXTRACT(dow FROM t), t::time

引用the manual on EXTRACT():

dow
The day of the week as Sunday(0) to Saturday(6)

isodow
The day of the week as Monday(1) to Sunday(7)

回答评论中的问题

I'm only interested in ordering them relative to the current date. Ie if it's tuesday, I want tuesday first, monday last.

“今天”午夜结束:

ORDER BY (EXTRACT(dow FROM t)::int + 7 - EXTRACT(dow FROM now())::int) % 7
         ,t::time

使用 modulo operator % 根据“今天”移动日期。
使用 dow 而不是 isodow，因为以 0 开头会使 % 更简单。