Обсуждение: Date calculation

Hi,

v9.6.6

Is there a built in function to calculate, for example, next Sunday?

For example,

postgres=# select current_date, next_dow(current_date, 'Sunday');
     date    |    date
------------|------------
  2019-01-31 | 2019-02-03
(1 row)

Thanks

-- 
Angular momentum makes the world go 'round.

On Thu, Jan 31, 2019 at 02:11:14PM -0600, Ron wrote:
> Hi,
> 
> v9.6.6
> 
> Is there a built in function to calculate, for example, next Sunday?
> 
> For example,
> 
> postgres=# select current_date, next_dow(current_date, 'Sunday');
>     date    |    date
> ------------|------------
>  2019-01-31 | 2019-02-03
> (1 row)

Uh, this worked:

    SELECT date_trunc('week', CURRENT_TIMESTAMP) + '6 days';
            ?column?
    ------------------------
     2019-02-03 00:00:00-05

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On 1/31/19 2:15 PM, Bruce Momjian wrote:
> On Thu, Jan 31, 2019 at 02:11:14PM -0600, Ron wrote:
>> Hi,
>>
>> v9.6.6
>>
>> Is there a built in function to calculate, for example, next Sunday?
>>
>> For example,
>>
>> postgres=# select current_date, next_dow(current_date, 'Sunday');
>>      date    |    date
>> ------------|------------
>>   2019-01-31 | 2019-02-03
>> (1 row)
> Uh, this worked:
>
>     SELECT date_trunc('week', CURRENT_TIMESTAMP) + '6 days';
>             ?column?
>     ------------------------
>      2019-02-03 00:00:00-05

Perfect!  All I had to do was cast that as DATE...

Thanks

-- 
Angular momentum makes the world go 'round.

On Thu, Jan 31, 2019 at 02:21:52PM -0600, Ron wrote:
> On 1/31/19 2:15 PM, Bruce Momjian wrote:
> >On Thu, Jan 31, 2019 at 02:11:14PM -0600, Ron wrote:
> >>Hi,
> >>
> >>v9.6.6
> >>
> >>Is there a built in function to calculate, for example, next Sunday?
> >>
> >>For example,
> >>
> >>postgres=# select current_date, next_dow(current_date, 'Sunday');
> >>     date    |    date
> >>------------|------------
> >>  2019-01-31 | 2019-02-03
> >>(1 row)
> >Uh, this worked:
> >
> >    SELECT date_trunc('week', CURRENT_TIMESTAMP) + '6 days';
> >            ?column?
> >    ------------------------
> >     2019-02-03 00:00:00-05
> 
> Perfect!  All I had to do was cast that as DATE...

Oh, right, you want date, so use:

    SELECT date_trunc('week', CURRENT_DATE) + '6 days';
            ?column?
    ------------------------
     2019-02-03 00:00:00-05

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

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

>>>>> "Bruce" == Bruce Momjian <bruce@momjian.us> writes:

 Bruce> Oh, right, you want date, so use:

 Bruce>    SELECT date_trunc('week', CURRENT_DATE) + '6 days';

Three major things wrong with this:

1. If you do this on Sunday, it gives you the current day not the _next_
Sunday.

2. If you try and do this for other days of the week it doesn't work at
all, instead giving you the specified day of the current week whether or
not it's before or after the current day.

3. It's letting PG cast the date to a timestamptz, which is inefficient,
possibly incorrect, and mutable; you want to force it to cast to
timestamp without timezone instead. (A good rule of thumb is that you
virtually never want to cast dates to timestamptz; the natural cast from
date is to timestamp _without_ timezone.)

-- 
Andrew (irc:RhodiumToad)