Search code examples
sqlruby-on-railsruby-on-rails-3postgresqldatetime

SQL in postgres convert datetime for recurring event to future datetime

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.

Solution

  • 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)

    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.