SQL in postgres convert datetime for recurring event to future datetime
datetime, postgresql, ruby-on-rails, ruby-on-rails-3, sql
Solution
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 `dow`instead of `isodow`, because starting with `0` makes `%` simpler.
Problem
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.