Postgres中的SQL将重复事件的日期时间转换为将来的日期时间

Question

我仅使用DATETIME跟踪表中每周发生的定期事件。我只关心时间和星期几。

我需要能够将设置的DATETIME转换为当前或即将到来的未来时间。

IE如何将2013-02-22 12:00:00使用当前日期存储的日期转换为下一个出现的日期？即下个星期五的12:00:002013-03-01 12:00:00左右，这样我就可以按日期订购活动了？

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

更新：

从欧文（Erwin），我得到了类似的东西：

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

除了我得到的第一个日期是星期一，跳过了我所知道的在星期日之前存在的事件（似乎排在最后），这些命令似乎可以排序。

Answer 1

最好的选择是存储atimestamp或timestamptz（timestamop with timezone）。如果您曾经或曾经必须处理多个时区，请进行设定timestamptz并定义您是否要使用当地时间或UTC或任何其他时间进行操作。

演示如何有效地将时间戳转换为当前星期（一周中的同一天和时间）。假设timestamp在这里：

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

诀窍是计算interval相应星期的开始与给定timestamp星期之间的，并借助将该值添加到当前星期的开始date_trunc()。

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

或者，只给一个星期增加一个星期timestamp：

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

在EXTRACT（）上引用手册：

dow
星期几是星期日（0）到星期六（6）

isodow
星期几是星期一（1）到星期日（7）

在评论中回答问题

我只想相对于当前日期订购它们。也就是说，如果是星期二，我想先是星期二，最后是星期一。

在“今天”的午夜包装：

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

使用模运算符%根据“今天”移动日期。 使用dow替代的isodow，因为首先是0品牌%简单。