>>>>> "Ron" == Ron <ronljohnsonjr@xxxxxxxxx> 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)
