CODEWITHSUNDEEP
sqldatabaseansi-sqldatabase-agnostic
David542
David542

Reputation: 110512

Comparing various date/time types

Is it defined how a date should be compared to a timestamp with a timezone or a timestamp without a timezone? For example, something like:

SELECT
    DATE '2014-01-01' = TIMESTAMP WITH TIME ZONE '2014-01-01',
    DATE '2014-01-01' = TIMESTAMP WITHOUT TIME ZONE '2014-01-01',
    TIMESTAMP WITH TIME ZONE '2014-01-01' = TIMESTAMP WITHOUT TIME ZONE '2014-01-01'

I've seen various implementations, some that error and some that allow it. For example Postgres allows all three, whereas BigQuery only allows comparing a date to a timestamp without a timezone.

My thinking is that a date doesn't necessarily imply that the time with that day is 00:00:00 -- for example, if the tv air date of an episode is July 1, 2023, it doesn't necessarily mean that the air date is at midnight UTC. So I think in the strictest sense, equality doesn't make sense across various date/time types (as it might across various numeric types).

Upvotes: 0

Views: 70

Answers (1)

jarlh
jarlh

Reputation: 44795

ISO/IEC 9075-2:2023(E)

5.3 < literal >

<timestamp literal> ::=
  TIMESTAMP <timestamp string>

<timestamp string> ::=
  <quote> <unquoted timestamp string> <quote>

<unquoted timestamp string> ::=
  <unquoted date string> <space> <unquoted time string>

<unquoted date string> ::=
  <date value>

<unquoted time string> ::=
  <time value> [ <time zone interval> ]

<time zone interval> ::=
  <sign> <hours value> <colon> <minutes value>

Which means a TIMESTAMP literal can look like

TIMESTAMP'2023-06-21 08:03:33'
TIMESTAMP'2023-06-21 08:03:33.123'
TIMESTAMP'2023-06-21 08:03:33+10:00'

4.6 Datetimes and intervals

Items of type datetime are comparable only if they have the same < primary datetime field >s.

I.e. a TIMESTAMP and a DATE are not comparable, because they do not have the same primary datetime fields.

<primary datetime field> ::=
  <non-second primary datetime field>
  | SECOND

<non-second primary datetime field> ::=
  YEAR
| MONTH
| DAY
| HOUR
| MINUTE

Upvotes: 1

Related Questions