[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Date calculation
From:       Andrew Gierth <andrew () tao11 ! riddles ! org ! uk>
Date:       2019-01-31 21:00:48
Message-ID: 875zu4a6ox.fsf () news-spur ! riddles ! org ! uk
[Download RAW message or body]

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

[prev in list] [next in list] [prev in thread] [next in thread]