Allow specification of the data types
TIME WITH TIME ZONE
TIMESTAMP WITH TIME ZONE
DATETIME WITH TIME ZONE
The objective is conformance with SQL:2003 standard feature
F411, "Time zone specification". Oracle extensions are desirable,
they'll be listed in a separate section after the standard
descriptions.
Syntax for data type definition
-------------------------------
TIME [ { WITH | WITHOUT } TIME ZONE ]
TIMESTAMP [ { WITH | WITHOUT } TIME ZONE ]
DATETIME [ { WITH | WITHOUT } TIME ZONE ]
The default is WITHOUT TIME ZONE, that is,
"TIME" and "TIME WITHOUT TIME ZONE" are the same thing.
If WL#946 "TIME/TIMESTAMP/DATETIME with fractional seconds"
is implemented first, then the precision comes before
{WITH | WITHOUT}, e.g.
TIMESTAMP [(p)] [ { WITH | WITHOUT } TIME ZONE ]
This syntax may appear wherever a data type specification
may appear: in a column definition, in a stored-routine
for parameter or declared variable or returns, in CAST.
Example:
CREATE TABLE t (s1 TIMESTAMP WITH TIME ZONE);
The with-time-zone data types are separate from the
without-time-zone data types, but comparisons and
assignments are always possible.
Syntax for literal
------------------
A with-time-zone literal ends with a "displacement",
a term that we will prefer, rather than "offset".
The displacement comes right after the time and
looks like: {+|-}hh:mm, where hh and mm are
unsigned integers. Displacement is in fact an INTERVAL
value between '-12:59' and '+14:00'. MySQL allows
SET TIME_ZONE = '-12:59'; /* etc. */
and the same rules will apply for displacement
as for @@time_zone. Spaces are illegal.
Sometimes you'll see this sort of thing on emails:
"> On Fri, 2006-12-08 at 19:44 +0100, Stefan Hinz wrote:"
Notice the space after '19:44'. MySQL will allow that.
Notice the displacement has no ':'. MySQL will allow that.
For datetimes, there's what MySQL calls a "relaxed" syntax.
For example, '19970523091528' is a valid TIMESTAMP. So
'19970523091528+00:00' is a valid TIMESTAMP WITH TIME ZONE,
and so is '19970523091528+0000'. But the '+' or '-' is
compulsory, and it is no longer true that (quoting the
manual) "Any punctuation character may be used as the
delimiter between date parts or time parts" -- at the
very least MySQL should disallow "+" and "-", and for
WL#946 purposes MySQL should also disallow ".", as
delimiters.
In strict mode, an illegal displacement (for example
'2001-01-01 00:00:00+99:7A') will cause an error,
SQLSTATE='22009', message = "Data Exception: Invalid
Time Zone Displacement Value". In non-strict mode,
the illegal displacement will be converted to UTC, or
the illegal displacement will be converted to @@time_zone,
or the whole string will be converted to
'0000-00-00 00:00:00+00:00', nobody cares which.
Examples:
TIMESTAMP '1997-01-01 22:22:22+00:30'
TIME '1:1:1 +4:0'
In the example, TIME and TIMESTAMP are not compulsory.
But they're desirable, for reasons given in the next
section.
Using TIME and TIMESTAMP for literals
-------------------------------------
MySQL allows use of TIME or TIMESTAMP for literals,
for example TIME '00:00:00' and TIMESTAMP '1999-01-01 00:00:00'.
But it ignores the words -- TIME '00:00:00' is treated as
VARCHAR(8). MySQL should stop ignoring the words.
Consider:
Moscow's usual displacement is '+03:00'.
Berlin's usual displacement is '+01:00'.
So if it's 3:00 in Moscow, it's 1:00 in Berlin.
Therefore this expression should be true:
TIME '03:00:00+03:00' = TIME '01:00:00+01:00'
But it will be false if MySQL thinks the two
values are strings and compares byte-by-byte.
Notice, in fact, that TIME '03:00:00+03:00' is a
TIME WITH TIME ZONE rather than a TIME. There is
no requirement in the standard for the syntax
TIME WITH TIME ZONE '03:00:00+03:00'. It's not
necessary, MySQL can figure out that it's a
with-time-zone data type by looking for + or -.
Storing as UTC component + Displacement
---------------------------------------
Suppose I'm in Edmonton, 7 hours west of ("behind")
Greenwich, and the clock on my wall says "10:30".
I can tell the time two ways:
(1) "It's 10:30 here and I'm -7 hours from Greenwich", or
(2) "It's 17:30 in Greenwich and I'm -7 hours from Greenwich".
Either way, I'm conveying the same amount of information.
If UTC = Greenwich today (it often is), I'd say this briefly as:
Way 1: TIMESTAMP '2007-02-22 10:30:00-07:00'
Way 2: TIMESTAMP '2007-02-22 17:30:00-07:00'
Way 1 is what I want to see because it's "normal",
it's what ISO 8601 says, it's what I want to input/output.
Way 2 is far better for comparisons, though. If I want
to ask "is 10:30 in Edmonton the same as 00:30 in Moscow?"
If I express the times in Way 1, I'm asking
'10:30:00-07:00' = '00:00:00+03:00'?
If I express the times in Way 2, I'm asking
'17:30:00-07:00' = '17:30:00+03:00'?
Way 2 is better because any left-to-right string-comparison
of the main part -- the "UTC component" -- gives the answer
immediately.
The other reason that I have to know the Way 1 / Way 2
difference is: the SQL standard document often assumes "Way 2".
For example it says:
"In comparing values of a data type WITH TIME ZONE, the value
of the time zone displacement is disregarded."
and
"NOTE 74 — If
Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.