SQL in postgres convert datetime for recurring event to future datetime

Question

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.

score 4 · Accepted Answer · edited May 23 '17 at 11:56

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.

I used something like that from your example and it seems to order them but offset by one day. See update thanks — ere, Feb 23 '13 at 14:41
@ere: The week according to ISO starts with Monday, not Sunday. I added more to my answer. — Erwin Brandstetter, Feb 23 '13 at 14:58
Thanks, I'm only interested in ordering them relative to the current date. Ie if it's tuesday, I want tuesday first, monday last. — ere, Feb 23 '13 at 15:36
@ere: Added another answer assuming you want to wrap with the start of "today", not with "now". — Erwin Brandstetter, Feb 23 '13 at 16:06

score 1 · Answer 2 · edited Sep 12 '13 at 17:30

1

Keep using the datetime. It's simple and gives you flexibility. You can use the extract function to get your time of day and day of week results. This page will help you. http://www.postgresql.org/docs/9.3/static/functions-datetime.html

edited Sep 12 '13 at 17:30

plaes

31,788
11
91
89

answered Feb 23 '13 at 13:36

Dan Bracuk

20,699
4
26
43

Please [link to the current manual](http://meta.stackexchange.com/questions/108714/best-way-to-reference-the-postgresql-manual). Postgres 8.1 has been unsupported for a long time now. – Erwin Brandstetter Feb 23 '13 at 14:14

SQL in postgres convert datetime for recurring event to future datetime

2 Answers2

Answer to question in comment

Linked