Postgres Pro
Downloads
Home   >   mailing lists

Thread: Date calculation

Date calculation

From
Ron
Date:
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.

Re: Date calculation

From
Bruce Momjian
Date:
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 +

Re: Date calculation

From
Ron
Date:
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.

Re: Date calculation

From
Bruce Momjian
Date:
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 +

Re: Date calculation

From
Andrew Gierth
Date:
>>>>> "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)

Re: Date calculation

From
Andrew Gierth
Date:
>>>>> "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)