Postgres中的SQL将重复事件的日期时间转换为将来的日期时间 [英] SQL in postgres convert datetime for recurring event to future datetime
问题描述
我仅使用DATETIME跟踪表中每周发生的定期事件。我只关心时间和星期几。
我需要能够将设置的DATETIME转换为当前或即将到来的未来时间。
IE如何使用当前日期转换存储为 2013-02-22 12:00:00 的日期到下一次发生？即下个星期五的12:00:00或 2013-03-01 12:00:00 ，这样我就可以按日期订购活动了？
或者我可以将时间和星期几分别存储为数字0-6。
更新：
从欧文（Erwin）我得到了类似的东西：
Event.order（ date_trunc（'week '，now（）:: timestamp）+（start_at-date_trunc（'week'，start_at）））
除了我得到的第一个日期是星期一，它跳过了我所知道的最后一个星期日发生的事件之外，似乎订购了它们。
您最好的选择是存储时间戳或 timestamptz （ timestamop与时区）。如果您曾经或曾经必须处理多个时区，请设置 timestamptz 并定义您是否要使用当地时间或UTC或其他时间进行操作。此相关答案中的更多详细信息：
完全忽略Rails和PostgreSQL中的时区
演示如何将时间戳有效地转换为当前周（一周中的同一天和时间）。假设在此处使用时间戳：
SELECT date_trunc（'week'，now（ ）:: timestamp）+（t-date_trunc（'week'，t））
FROM（选择'2013-02-15 12:00:00':: timestamp AS t）x;
诀窍是计算之间的间隔相应星期的开始和给定的时间戳，并在 date_trunc（） 。。 p>
ISO周从星期一开始，最后一个星期天。
或者，给指定的时间戳：
SELECT t +间隔 1周；
如果您只是想
ORDER BY（t-date_trunc（'week'，t））
如果您想将星期日放在第一位（轮班日）：
ORDER BY（（（t +间隔'1d'）-date_trunc（'week'，（t +间隔'1d'）））
或更简单：
按提取顺序（来自t的dow） ，t :: time
$ p $ b
dow
星期几，从星期日（0）到星期六（6）
等价物
星期几，从星期一（1）到星期日（7）
在评论中提问的问题
我只是有兴趣相对于当前日期订购它们。即
，如果是星期二，我要在星期二第一，最后一个星期一。
在今天的午夜打包：
ORDER BY（EXTRACT（dow FROM t）:: int + 7-EXTRACT（dow FROM now（））:: int）％ 7
，t :: time
使用取模运算符％ 可根据到今天。
使用 dow 代替 isodow ，因为以<$ c开头$ c> 0 使％更简单。
I'm keep track of recurring weekly events in a table using just a DATETIME. I only care about the TIME and the day of the week it falls on.
I need to be able to convert the set DATETIME into the current or upcoming future one.
IE How can I convert a date stored as 2013-02-22 12:00:00 using the current date to the next occurrence? Ie this next Friday at 12:00:00 or 2013-03-01 12:00:00 so that I can then order events by date?
Or I could store the TIME and day of the week separately as a number 0-6.
UPDATE:
From Erwin I got something like:
Event.order("date_trunc('week', now()::timestamp) + (start_at - date_trunc('week', start_at))")
Which seems order them except that the first dates I get are Monday skipping over events I know exist for Sunday which it puts as last.
Your best choice is to store a timestamp or timestamptz (timestamop with time zone). If you have or ever will have to deal with more than one time zone, make that timestamptz and define whether you want to operate with local time or UTC or whatever. More details in this related answer:
Ignoring timezones altogether in Rails and PostgreSQL
Demo how to transpose a timestamp into the current week efficiently (same day of week and time). Assuming timestamp here:
SELECT date_trunc('week', now()::timestamp) + (t - date_trunc('week', t))
FROM (SELECT '2013-02-15 12:00:00'::timestamp AS t) x;
The trick is to compute the interval between the start of the corresponding week and the given timestamp and add that to the start of the current week with the help of date_trunc().
The ISO week starts with Monday, putting Sunday last.
Or, to just add a week to a given timestamp:
SELECT t + interval '1 week';
If You just want to ORDER BY, you only need the interval:
ORDER BY (t - date_trunc('week', t))
If you want to put Sunday first (shifting days):
ORDER BY ((t + interval '1d') - date_trunc('week', (t + interval '1d'))
Or simpler:
ORDER BY EXTRACT(dow FROM t), t::time
Quoting 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)
Answer to question in comment
I'm only interested in ordering them relative to the current date. Ie if it's tuesday, I want tuesday first, monday last.
Wrapping at midnight of "today":
ORDER BY (EXTRACT(dow FROM t)::int + 7 - EXTRACT(dow FROM now())::int) % 7
,t::time
Using the modulo operator % to shift the day according to "today".
Using dowinstead of isodow, because starting with 0 makes % simpler.
这篇关于Postgres中的SQL将重复事件的日期时间转换为将来的日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
