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 option is to store a timestamp
or timestamptz
(timestamp with time zone
). If time zones can be involved in any way, make that timestamptz
and define whether you want to operate with local time or UTC or whatever. See:
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)
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.