From: | Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> |
---|---|
To: | Ron <ronljohnsonjr(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Date calculation |
Date: | 2019-01-31 20:53:07 |
Message-ID: | 87d0oca8l0.fsf@news-spur.riddles.org.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>>>>> "Ron" == Ron <ronljohnsonjr(at)gmail(dot)com> writes:
Ron> Hi,
Ron> v9.6.6
Ron> Is there a built in function to calculate, for example, next
Ron> Sunday?
No, but such things aren't hard to calculate using the available
primitives.
To get "next Xday", for example, you can add 7 days and then do
"previous or current Xday". In turn, "previous or current Xday" can be
done by subtracting (X-Monday), doing date_trunc 'week', and adding
(X-Monday) again.
select current_date,
date_trunc('week', (current_date + 7 - 6)::timestamp)::date + 6;
current_date | ?column?
--------------+------------
2019-01-31 | 2019-02-03
(1 row)
If you do this sort of thing a lot, then define your own functions for
it:
-- create this to override the cast to timestamptz that otherwise messes
-- things up:
create function date_trunc(text,date)
returns date language sql immutable
as $f$
select date_trunc($1, $2::timestamp)::date;
$f$;
-- perfect hash function for weekday names, with Monday=0
-- (accepts upper, lower or mixed case)
create function dayno(text)
returns integer
language sql immutable
as $f$
select (( ((ascii(substring($1 from 3)) & 22)*10)
# (ascii($1) & 23) )*5 + 2) % 7;
$f$;
create function next_dow(start_date date, day_name text)
returns date language sql immutable
as $f$
select date_trunc('week', (start_date + 7 - dayno(day_name)))
+ dayno(day_name);
$f$;
select current_date,
next_dow(current_date, 'Thursday'),
next_dow(current_date, 'Friday');
current_date | next_dow | next_dow
--------------+------------+------------
2019-01-31 | 2019-02-07 | 2019-02-01
--
Andrew (irc:RhodiumToad)
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Gierth | 2019-01-31 21:00:48 | Re: Date calculation |
Previous Message | Bruce Momjian | 2019-01-31 20:23:40 | Re: Date calculation |