Comparing various date/time types

Question

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

If it's defined anywhere I guess it would be in an ISO standard — Nick.Mc, Jun 20 '23 at 02:36
@Nick.McDermaid does SQLServer support comparing various different date/time types without explicitly casting? — David542, Jun 20 '23 at 02:37
Yes. But it always implicitly casts them to match behind the scenes. This shows which data types can be implicitly casted and which will throw an error https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-ver16 . I'm not sure which wau it casts them though. — Nick.Mc, Jun 20 '23 at 02:40
Your TIMESTAMP literals are all invalid. And you can't compare dates with timestamps. (According to ANSI SQL.) — jarlh, Jun 20 '23 at 06:12
Unfortunately, most databases implement different date and time data types, different arithmetic, and also have strange quirks with them (MySQL is notorious for this). You won't find "database agnostic" logic for them. — The Impaler, Jun 20 '23 at 13:58
@jarlh which part is invalid? Here is the query running in Postgres -- https://gyazo.com/edef80d2cacd25adbdf4cf2bdbd342c4. — David542, Jun 21 '23 at 00:32

jarlh · Accepted Answer · 2023-06-21T06:19:51.773

1

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

edited Jun 21 '23 at 06:19

answered Jun 21 '23 at 06:07

jarlh

42,561
8
45
63

I see, thanks for the clarification. So is the way postgres does it totally nonstandard? Maybe that's confusing the data type with the literal value: https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm#i1006081 – David542 Jun 21 '23 at 06:17
does the comparison definition allow comparing a `DATETIME` and `TIMESTAMP` then? I.e., two timestamps, one with a timezone and one without? – David542 Jun 21 '23 at 06:21
Yes, you can compare two timestamps, one with a timezone and one without. – jarlh Jun 21 '23 at 07:36

Comparing various date/time types

1 Answers1