SELECT record_id, rec_date
, CASE WHEN rec_date::time < '08:00' THEN 'Night'
WHEN rec_date::time >= '20:00' THEN 'Night'
ELSE 'Day' END AS indicator
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 SYMMETRIC. But
BETWEEN 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,
07 are constants.
Applied to your case naively, you would get day and night inverted, because
BETWEEN SYMMETRIC 20 AND 07 ends up being evaluated as
BETWEEN 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 without
SYMMETRIC, 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:
CASE WHEN date_part('hour', ts) BETWEEN '08' AND '19' -- adjusted
THEN 'Day' ELSE 'Night' END AS indicator
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 pointless
SYMMETRIC, 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.
I would not call a timestamp column "record_date", as
date is a different basic data type than
timestamp. 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: