Search Postgresql Archives

Re: Date calculation

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux