There is a timestamp
column I use to indicate whether a row was created during day or night time. My code is the following, but for some reason I only get 'DAY' as outcome. Am I not formatting the values right?
select record_id, rec_date,
case when date_part('hour', rec_date) between 20 and 07 then 'Night'
else 'Day' end as Indicator
from records;
The rec_date
column is a timestamp where I can see values such as 2019-11-20 21:34:02.000000
– which should get a 'Night'
indicator.
Best Answer
Why?
It's a matter of date-math rather than date-format. You want to do the math correctly and efficiently. The format of 'Day' and 'Night' are not in question.
McNets already shed some light on
BETWEEN
vs.BETWEEN SYMMETRIC
. ButBETWEEN SYMMETRIC 20 AND 07
would still be dead ugly, slow, and incorrect, ultimately.SYMMETRIC
only makes sense with parameterized bounds where you don't know which will be greater ahead of time. Not the case,20
and07
are constants.Applied to your case naively, you would get day and night inverted, because
BETWEEN SYMMETRIC 20 AND 07
ends up being evaluated asBETWEEN 07 AND 20
(just more expensively).OK, easily fixed by switching 'Day' and 'Night'. But now, the times 20:** and 07:** would be tagged 'Day'.
BETWEEN
, with or withoutSYMMETRIC
, includes upper and lower bound. That's why it is almost always the wrong tool to use with timestamps. In this particular case,date_part()
happens to counter the built-in issue with including both bounds to some extent. Either way, to match your original intent, it would have to be:The query with the expression
date_part('hour', rec_date) BETWEEN SYMMETRIC 20 AND 07
is roughly twice as expensive as my suggestion. The larger part due to the pointlessSYMMETRIC
, the smaller part due to the function being more expensive than the cast.The suggested expression is much less likely to be misunderstood than the devious
BETWEEN
, as it makes clear which bounds are included.Asides
I would not call a timestamp column "record_date", as
date
is a different basic data type thantimestamp
. More potential for confusion.If your actual data type happens to be
timestamptz
(or, possibly, in any case), you may have to define where in the world it's supposed to be "day" or "night". See: