Date & Time

date

This represents a calendar date and specifies a year, month, and day. Its specific format (i.e. month/day/year, day/month/year, year-month-day, etc.) depends on the locale of the database. Some database systems allow the administrator to set a specified “date style” that will be used in lieu of the locale’s date format, if such separate behavior is desired.

time

This represents the time of day and does not include a date. Like date, the display format of time fields will depend on the locale of the database.

This data type may also be specified as time with time zone, which includes a time zone. Time zones can be specified by their three-letter code, like UTC or EST, or by the difference from UTC, like -05. 3:00 PM, Eastern Standard Time (United States), for example, could be represented as 15:00:00-05.

timestamp

The timestamp type combines the date and time types and may also include a time zone. An example value might be 2007-12-31 23:59:59 UTC.

Null Values

The NULL value is not actually a data type, but it should be mentioned here for completeness. NULL can be accepted as a value for a column of any data type, so long as that column is not constrained to be NOT NULL.

The NULL value is used by the database to signify that a certain field has no value, and database applications often use it when a field is considered optional. In conditional SQL statements, the IS NULL clause can be used to determine whether a field contains a NULL value.

NULL has an interesting and sometimes counter-intuitive effect on text strings in certain database systems. In PostgreSQL, for example, a string concatenation containing a NULL value will result in a NULL string, even if other parts of the concatenation are not NULL values. This is illustrated in Figure 3.

In PostgreSQL, this behavior can be overcome by using the COALESCE() function, whose sole purpose is to return the first non-NULL parameter. This is shown in Figure 4.

COALESCE() is especially useful in database application programming, when it is not known whether a value will be NULL prior to a concatenation or another use of a possibly-NULL value in a string manipulation process.

Next Page: Implementation-Specific Data Types