SQL Bible [Electronic resources] نسخه متنی

Once Upon a Time: Date and Time Data Types

Handling dates and times is probably one of
the most complicated and inconsistent topics in SQL. According to our personal
experiences, operations with dates often cause confusion and even frustration
not only among database users, but in the developers' community, too.

Introduction to complex data
types

One of the problems is dates are not
actually what they look like. So far we were talking only about simple data
types that store one value per row. Date and time data types hold a number of
elements (year, day, month, hour, etc.). In programming, such data types are
called complex and are often represented as structures.
When returned as a database query result, date and time fields appear like
strings, but in fact they rather are parts of structures, similar to ones in
the example below. (We don't use any specific programming language in this
example, but rather some kind of pseudocode.)

STRUCTURE DATE { YEAR
DECIMAL(4,0), MONTH DECIMAL(2,0), DAY DECIMAL(2,0) } 

STRUCTURE TIME { HOUR
DECIMAL(2,0), MINUTE DECIMAL(2,0), SECOND DECIMAL(5,2) } 

STRUCTURE DATETIME { YEAR
DECIMAL(4,0), MONTH DECIMAL(2,0), DAY DECIMAL(2,0), HOUR DECIMAL(2,0), MINUTE
DECIMAL(2,0), SECOND DECIMAL(5,2) }

The displayed value just formats and
concatenates the fields of this structure. For example, for the
YYYY/DD/MM format, the pseudocode may
look like this:

CONCAT(CAST(DATETIME.YEAR,
STRING), '/', CAST(DATETIME.DAY, STRING), '/', CAST(DATETIME.MONTH,
STRING))

These structures should also have some
methods to handle situations when users want to display dates and times in
different formats, for example to display time on the 12- or 24-hour scale,
show day of week for a certain date, display century, convert it into a
different time zone, and so on.

We'll talk more about complex data types
later in this chapter.

Date and time
implementations

As we've mentioned before, date and time
data types are mandated by SQL99 and handled by different RDBMS implementations
quite in a different way. Date and time data types are summarized in
Table
3-5.

SQL99

SQL99 supports
DATE,
TIME,
TIMESTAMP,
TIME
WITH
TIME
ZONE, and
TIMESTAMP
WITH
TIME
ZONE data types.

DATE data type is a structure
that consists of three elements: year, month, and day. The year is a four-digit
number that allows values from 0000 through 9999; the month is a two-digit
element with values from 01 through 12; and the day is another two-digit figure
with range from 01 through 31. SQL99 does not have any strict rules on how to
implement
DATE internally, so vendors can make
their own decisions. One vendor could choose something similar to the
structures above; others could implement characters, numbers with different
scale, and so on.

TIME consists of hour, minute,
and second components. The hour is a number from 00 to 23, the minute is a
two-digit number from 00 to 59, and the second is either another integer from
00 to 61 or a decimal number with scale of 5 and precision of 3 that can hold
values from 00.000 to 61.999.

TIMESTAMP is a combination of
DATE and
TIME data types and includes year,
month, day, hour, minute, and second.

TIME
WITH
TIME
ZONE is basically an improvement to
the
TIME data type. It stores time zone
information in addition to standard
TIME elements.

TIMESTAMP
WITH
TIME
ZONE is an extension to the
TIMESTAMP with
information on time zone.

Oracle
9i

Oracle has
DATE,
TIMESTAMP,
TIMESTAMP
WITH
TIME
ZONE, and
TIMESTAMP
WITH
LOCAL
TIME
ZONE data types.

DATE is a slightly confusing data
type, because in spite of its name it stores both date and time information and
rather corresponds to SQL99
TIMESTAMP. In addition to standard
SQL99
TIMESTAMP fields (year, month, date,
hour, minute, and second), it also includes century.

TIMESTAMP data type is
practically same as
DATE, but you can specify an optional
precision for the number of digits in the fractional part of seconds. The valid
values are numbers from 0 to 9 with the default of 6.

TIMESTAMP
WITH
TIME
ZONE data type speaks for itself. The
only difference from
TIMESTAMP is a time zone displacement
included in its value, which is the difference in hours and minutes between
local time and Coordinated Universal Time (UTC), also known as Greenwich Mean
Time (GMT).

TIMESTAMP
WITH
LOCAL
TIME
ZONE data type is another variation
of
TIMESTAMP (or
TIMESTAMP
WITH
TIME
ZONE). The difference is that it is
normalized to the database time zone and the displacement value is not stored
with it, but when users query the column, the result is returned in user's
local session time zone.

This example will help you to
understand the difference between
TIMESTAMP and
TIMESTAMP
WITH
LOCAL
TIME
ZONE data types.

Imagine ACME expanded, and it now
has its offices all around the world. John is a head of Sales department
located in Seattle, WA, and needs to see all new invoices created yesterday at
each location. (We assume each location has its own database, and the databases
can communicate to each other.) The problem is, one of the offices is in New
York, another one is in Amsterdam, yet another is located in Bangkok, and so
on; of course, each location is in its own time zone. But as far as John is
concerned, he wants to see all new orders created whatever is considered to be
yesterday in Seattle, in other words, according to the Pacific Standard Time
(PST). Each of the mentioned offices belongs to a different time zone, without
even mentioning the Daylight Savings. So, 08/31/2003 6:00 PM in Seattle is
equivalent to 08/31/2003 9:00 PM in New York, 09/01/2003 3:00 AM in Amsterdam,
and 09/01/2003 8:00 AM in Bangkok. That means not only different days, but even
different months!

If the data type of
ORDER_HEADER.ORDHDR_INVOICEDATE_D
column is
TIMESTAMP, John would need a report
that programmatically translates all the different locations' invoice dates
into PST.

Life would definitely be easier if
we declared
ORDHDR_INVOICEDATE_D as a
TIMESTAMP
WITH
LOCAL
TIME
ZONE — then each time John queries
a remote database from Seattle the result is returned in his session's time,
that is, invoices created in Bangkok at 8 AM on September 1 (Thailand local
time) will appear as belonging to August 31 Pacific Standard Time.

INTERVAL
YEAR
TO
MONTH data type
stores a period of time using year and month fields.

INTERVAL
DAY
TO
SECOND data type can store a period
of time in days, hours, minutes, and seconds.

DB2 UDB
8.1

DB2 has three standard SQL99 date and
time data types —
DATE,
TIME, and
TIMESTAMP.

DATE consists of year, month, and
day. The range of the year part is 0001 to 9999; month can be from 1 to 12; and
the day part ranges from 1 to 28, 29, 30, or 31, depending on the month.
DATE in DB2 is stored internally as a
string of four bytes. Each byte represents two decimal digits. The first two
bytes are for the year, the third is reserved for the month, and the fourth one
holds day value. The length of a
DATE column is 10 bytes to fit
character string representation in literals.

TIME data type is represented
internally as a string of three bytes — one byte for hours, one for minutes,
and one for seconds. Hour ranges from 0 to 24 (if value is 24, minutes and
seconds will be all zeroes). Minute and second components have to be from 0
through 59. The length of a
TIME column in DB2 is 8 bytes to
allow the appropriate length for a character string representation.

TIMESTAMP data type is a
combination of
DATE and
TIME elements plus a microsecond
component. The internal representation is a string of ten bytes (four
DATE bytes, three
TIME bytes, and additional three
bytes for microseconds). The length of a
TIMESTAMP column is 26 bytes.

MS SQL
Server 2000

MS SQL Server has two date and time data
types,
DATETIME and
SMALLDATETIME that both represent the
combination of date and time values, but have different ranges.

DATETIME can store values from
01/01/1753 to 12/31/9999 with accuracy of 0.00333 seconds. It is stored
internally as an eight-byte string. The first four bytes represent the number
of days before (or after) 01/01/1900, which is the system reference date. The
second four bytes store time in milliseconds passed since midnight.

SMALLDATETIME can hold
dates from January 1, 1900 to June 6, 2079, with accuracy to the minute. The
internal storage for that data type is four bytes. Again, the first portion
(two bytes) stores the number of days after the system reference date
(01/01/1900), and the second portion stores time (in minutes after
midnight).

Date and time literals

Date and time literals are
implementation-specific and vary significantly among different vendors.

Oracle
9i

Oracle lets you specify
DATE values as literals if they match
special database initialization parameter
NLS_DATE_FORMAT, which defaults to
DD-MON-YY. (Oracle
initialization parameters are usually handled by DBA and are not covered in
this book.) You can also use SQL99 literal standard (YYYY-MM-DD) with a
DATE prefix, or convert literals into
string using the Oracle function
TO_DATE (covered in
Chapter
10). The three following statements are valid date literals examples.
(SHIPMENT_ARRIVDATE_D is a
DATE field in Oracle ACME database.)

UPDATE shipment SET
shipment_arrivdate_d = '03-SEP-02' WHERE shipment_id_n = 30661;

UPDATE shipment SET
shipment_arrivdate_d = DATE '2003-09-02' WHERE shipment_id_n = 30661;

UPDATE
shipment SET shipment_arrivdate_d = TO_DATE('September, 02 2003', 'Month, DD
YYYY') WHERE shipment_id_n = 30661;

But these are illegal:

UPDATE shipment SET
shipment_arrivdate_d = 'YYYY-MM-DD' WHERE shipment_id_n = 30661;

UPDATE shipment SET
shipment_arrivdate_d = '03-SEP-02 23:12:45' WHERE shipment_id_n =
30661;

TIMESTAMP and
TIMESTAMP
WITH
TIME
ZONE data types also accept the
DD-MON-YY format for literals
(with optional
TIME part); in addition, you can
specify literals with a
TIMESTAMP prefix:

TIMESTAMP '1997-01-31
09:26:50.124' TIMESTAMP '1997-01-31 09:26:56.66 +02:00' TIMESTAMP '1999-04-15
8:00:00 -8:00' TIMESTAMP '1999-04-15 8:00:00 US/Pacific' TIMESTAMP '1999-10-29
01:30:00 US/Pacific PDT'

The first of these lines is for
TIMESTAMP data type, and the other
four are for
TIMESTAMP
WITH
TIME
ZONE.

Oracle gives you a great deal of
flexibility when specifying interval values as literals. Reference
Table
3-6 for examples.

DB2 UDB 8.1

The following formats for
DATE literals are recognized:
YYYY-MM-DD (ANSI/ISO),
MM/DD/YYYY (IBM US), and
DD.MM.YYYY (IBM Europe).

So, these three statements are
legal:

UPDATE shipment SET
shipment_arrivdate_d = '2003-09-02' WHERE shipment_id_n = 30661

UPDATE shipment SET
shipment_arrivdate_d = '09/02/2003' WHERE shipment_id_n = 30661

UPDATE shipment SET
shipment_arrivdate_d = '02.09.2003' WHERE shipment_id_n = 30661

But this one is not:

UPDATE shipment SET
shipment_arrivdate_d = '02-SEP-2003' WHERE shipment_id_n =
30661

The valid
TIME literal formats are:
HH.MM.SS (ANSI/ISO and IBM Europe),
HH:MM AM|PM (IBM USA), and
HH:MM:SS (Japanese Industrial
Standard). Also, trailing blanks may be included, and a leading zero may be
omitted from the hour part of the time; seconds may be omitted entirely:

'12.23.56' '23:15 AM'
'8:45'

The valid string formats for
TIMESTAMP literals are
YYYY-MM-DD-HH.MM.SS.NNNNNN and
YYYY-MM-DD
HH:MM:SS.NNNNNN:

UPDATE shipment SET
shipment_createdate_d = '2003-10-12-23.34.29' WHERE shipment_id_n = 30661

UPDATE shipment SET
shipment_createdate_d = '2003-10-12 23:34:29.345678' WHERE shipment_id_n =
30661

MS SQL Server

MS SQL Server is probably the
friendliest RDBMS in terms of handling date and time. It recognizes the date
and time literals enclosed in single quotation marks in many different formats.
For example:

'August 15, 2003' '15
August, 2003' '15-AUG-2003' '15 Aug, 2003' '030815' '2003/08/15' '08/15/03'
'14:30:24' '04:24 PM' '15 August, 2003 23:00' '15-AUG-2003
22:45:34.345'

All these (and many other) formats
are valid for both
DATETIME and
SMALLDATETIME.