Chapter 7 - Dates

Abstract

National date formats may be confusing when used in an international environment. This chapter focuses on the ISO-8601 standard used for dates and times, which uses Common Era (CE) dates, time of day, combined date and time of day, and time intervals. The year has at least four digits and 1875-05-20 has been fixed as a reference calendar date to the Common Era calendar. The month values are shown as two digits in the range of 01–12 where the month is not used by itself but has to have a year attached to it. The format for the month is “YYYY-MM,” and MySQL has an extension to this that uses “YYYY-MM-00,” where the zeros are a placeholder for an unspecified day within the month or the whole month. YYYY indicates the ISO week-numbering year, which is slightly different from the calendar year. Www is the week number prefixed by the letter “W,” starting from W01 to W53. The ISO standard allows both the “YYYY-MM-DD” and “YYYYMMDD” formats for complete calendar date representations. The SQL standards use the YYYY-MM-DD format, but vendors may allow other formats, usually national variants. The format of the ordinal date is “YYYY-DDD” or “YYYYDDD,” where DDD maps from 001 to 365 or 366 in leap years. This format is usually embedded in encoding schemes that depend on a timestamp of some kind. ISO 8601 uses the 24-hour clock system where a day starts at “00:00:00” (zero hour) and ends just before the start of the following day. Hours are two digits between 00 and 23, where “24” is used as the “00” of the following day.