Re: Date calculation - Mailing list pgsql-general

From Andrew Gierth
Subject Re: Date calculation
Date
Msg-id 87d0oca8l0.fsf@news-spur.riddles.org.uk
Whole thread Raw
In response to Date calculation  (Ron <ronljohnsonjr@gmail.com>)
List pgsql-general
>>>>> "Ron" == Ron  <ronljohnsonjr@gmail.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)


pgsql-general by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: Date calculation
Next
From: Andrew Gierth
Date:
Subject: Re: Date calculation