1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
5 >Date/Time Types</TITLE
8 CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
10 HREF="mailto:pgsql-docs@postgresql.org"><LINK
12 TITLE="PostgreSQL 7.4.1 Documentation"
13 HREF="index.html"><LINK
16 HREF="datatype.html"><LINK
18 TITLE="Binary Data Types"
19 HREF="datatype-binary.html"><LINK
22 HREF="datatype-boolean.html"><LINK
25 HREF="stylesheet.css"><META
27 CONTENT="2003-12-22T03:48:47"></HEAD
33 SUMMARY="Header navigation table"
43 >PostgreSQL 7.4.1 Documentation</TH
51 HREF="datatype-binary.html"
67 >Chapter 8. Data Types</TD
81 HREF="datatype-boolean.html"
95 NAME="DATATYPE-DATETIME"
96 >8.5. Date/Time Types</A
129 > supports the full set of
133 > date and time types, shown in <A
134 HREF="datatype-datetime.html#DATATYPE-DATETIME-TABLE"
141 NAME="DATATYPE-DATETIME-TABLE"
145 >Table 8-9. Date/Time Types</B
150 ><COL><COL><COL><COL><COL><COL><THEAD
174 >) ] [ without time zone ]</TT
179 >both date and time</TD
185 >1 microsecond / 14 digits</TD
194 >) ] with time zone</TT
199 >both date and time, with time zone</TD
205 >1 microsecond / 14 digits</TD
221 >-178000000 years</TD
251 >) ] [ without time zone ]</TT
256 >times of day only</TD
271 >) ] with time zone</TT
276 >times of day only, with time zone</TD
301 > was equivalent to <TT
305 >. This was changed for SQL compliance.
320 > accept an optional precision value
324 > which specifies the number of
325 fractional digits retained in the seconds field. By default, there
326 is no explicit bound on precision. The allowed range of
330 > is from 0 to 6 for the
349 > values are stored as double precision floating-point
350 numbers (currently the default), the effective limit of precision
351 may be less than 6. <TT
354 > values are stored as seconds
355 before or after midnight 2000-01-01. Microsecond precision is achieved for
356 dates within a few years of 2000-01-01, but the precision degrades for
357 dates further away. When <TT
360 > values are stored as
361 eight-byte integers (a compile-time
362 option), microsecond precision is available over the full range of
363 values. However eight-byte integer timestamps have a more limited range of
364 dates than shown above: from 4713 BC up to 294276 AD.
372 > types, the allowed range of
376 > is from 0 to 6 when eight-byte integer
377 storage is used, or from 0 to 10 when floating-point storage is used.
382 >time with time zone</TT
383 > is defined by the SQL
384 standard, but the definition exhibits properties which lead to
385 questionable usefulness. In most cases, a combination of
394 >timestamp without time
398 >timestamp with time zone</TT
400 provide a complete range of date/time functionality required by
411 > are lower precision types which are used internally.
412 You are discouraged from using these types in new
413 applications and are encouraged to move any old
414 ones over when appropriate. Any or all of these internal types
415 might disappear in a future release.
422 NAME="DATATYPE-DATETIME-INPUT"
423 >8.5.1. Date/Time Input</A
426 > Date and time input is accepted in almost any reasonable format, including
435 For some formats, ordering of month, day, and year in date input is
436 ambiguous and there is support for specifying the expected
437 ordering of these fields. Set the <VAR
444 > to select month-day-year interpretation,
448 > to select day-month-year interpretation, or
452 > to select year-month-day interpretation.
458 > is more flexible in
459 handling date/time input than the
465 HREF="datetime-appendix.html"
468 for the exact parsing rules of date/time input and for the
469 recognized text fields including months, days of the week, and
473 > Remember that any date or time literal input needs to be enclosed
474 in single quotes, like text strings. Refer to
476 HREF="sql-syntax.html#SQL-SYNTAX-CONSTANTS-GENERIC"
483 > requires the following syntax
500 > in the optional precision
501 specification is an integer corresponding to the number of
502 fractional digits in the seconds field. Precision can be
513 > types. The allowed values are mentioned
514 above. If no precision is specified in a constant specification,
515 it defaults to the precision of the literal value.
530 HREF="datatype-datetime.html#DATATYPE-DATETIME-DATE-TABLE"
532 > shows some possible
541 NAME="DATATYPE-DATETIME-DATE-TABLE"
545 >Table 8-10. Date Input</B
563 >unambiguous in any <VAR
572 >ISO 8601; January 8 in any mode
573 (recommended format)</TD
596 rejected in other modes</TD
602 >January 2, 2003 in <TT
606 February 1, 2003 in <TT
610 February 3, 2001 in <TT
620 >January 8 in any mode</TD
626 >January 8 in any mode</TD
632 >January 8 in any mode</TD
641 > mode, else error</TD
647 >January 8, except error in <TT
656 >January 8, except error in <TT
665 >ISO 8601; January 8, 1999 in any mode</TD
671 >ISO 8601; January 8, 1999 in any mode</TD
677 >year and day of year</TD
687 >January 8, 99 BC</TD
689 >year 99 before the Common Era</TD
713 > The time-of-day types are <TT
719 >) ] without time zone</TT
734 >time without time zone</TT
738 > Valid input for these types consists of a time of day followed
739 by an optional time zone. (See <A
740 HREF="datatype-datetime.html#DATATYPE-DATETIME-TIME-TABLE"
744 HREF="datatype-datetime.html#DATATYPE-TIMEZONE-TABLE"
746 >.) If a time zone is
747 specified in the input for <TT
749 >time without time zone</TT
751 it is silently ignored.
756 NAME="DATATYPE-DATETIME-TIME-TABLE"
760 >Table 8-11. Time Input</B
817 >same as 04:05; AM does not affect value</TD
826 >same as 16:05; input hour must be <= 12</TD
871 >time zone specified by name</TD
879 NAME="DATATYPE-TIMEZONE-TABLE"
883 >Table 8-12. Time Zone Input</B
904 >Pacific Standard Time</TD
913 >ISO-8601 offset for PST</TD
922 >ISO-8601 offset for PST</TD
931 >ISO-8601 offset for PST</TD
940 >Military abbreviation for UTC</TD
964 >8.5.1.3. Time Stamps</A
976 > Valid input for the time stamp types consists of a concatenation
977 of a date and a time, followed by an optional
985 optional time zone. Thus
988 CLASS="PROGRAMLISTING"
989 >1999-01-08 04:05:06</PRE
993 CLASS="PROGRAMLISTING"
994 >1999-01-08 04:05:06 -8:00</PRE
997 are valid values, which follow the <ACRONYM
1001 standard. In addition, the wide-spread format
1004 CLASS="PROGRAMLISTING"
1005 >January 8 04:05:06 1999 PST</PRE
1012 >timestamp [without time zone]</TT
1013 >, any explicit time
1014 zone specified in the input is silently ignored. That is, the
1015 resulting date/time value is derived from the explicit date/time
1016 fields in the input value, and is not adjusted for time zone.
1021 >timestamp with time zone</TT
1022 >, the internally stored
1023 value is always in UTC (Universal
1024 Coordinated Time, traditionally known as Greenwich Mean Time,
1028 >). An input value that has an explicit
1029 time zone specified is converted to UTC using the appropriate offset
1030 for that time zone. If no time zone is stated in the input string,
1031 then it is assumed to be in the time zone indicated by the system's
1035 > parameter, and is converted to UTC using the
1044 >timestamp with time
1046 > value is output, it is always converted from UTC to the
1050 > zone, and displayed as local time in that
1051 zone. To see the time in another time zone, either change
1060 HREF="functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT"
1065 > Conversions between <TT
1067 >timestamp without time zone</TT
1071 >timestamp with time zone</TT
1072 > normally assume that the
1075 >timestamp without time zone</TT
1076 > value should be taken or given
1080 > local time. A different zone reference can
1081 be specified for the conversion using <TT
1093 >8.5.1.4. Intervals</A
1102 > values can be written with the following syntax:
1105 CLASS="PROGRAMLISTING"
1136 > is a number (possibly signed);
1174 or abbreviations or plurals of these units;
1182 empty. The at sign (<TT
1185 >) is optional noise. The amounts
1186 of different units are implicitly added up with appropriate
1190 > Quantities of days, hours, minutes, and seconds can be specified without
1191 explicit unit markings. For example, <TT
1197 >'1 day 12 hours 59 min 10 sec'</TT
1201 > The optional precision
1205 > should be between 0 and 6, and
1206 defaults to the precision of the input literal.
1215 >8.5.1.5. Special Values</A
1224 > The following <ACRONYM
1227 >-compatible functions can be
1228 used as date or time values for the corresponding data type:
1238 >CURRENT_TIMESTAMP</TT
1246 >. The latter four accept an
1247 optional precision specification. (See also <A
1248 HREF="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT"
1256 > also supports several
1257 special date/time input values for convenience, as shown in <A
1258 HREF="datatype-datetime.html#DATATYPE-DATETIME-SPECIAL-TABLE"
1268 are specially represented inside the system and will be displayed
1269 the same way; but the others are simply notational shorthands
1270 that will be converted to ordinary date/time values when read.
1271 All of these values are treated as normal constants and need to be
1272 written in single quotes.
1277 NAME="DATATYPE-DATETIME-SPECIAL-TABLE"
1281 >Table 8-13. Special Date/Time Inputs</B
1312 >1970-01-01 00:00:00+00 (Unix system time zero)</TD
1326 >later than all other time stamps</TD
1340 >earlier than all other time stamps</TD
1360 >current transaction's start time</TD
1394 >midnight tomorrow</TD
1411 >midnight yesterday</TD
1425 >00:00:00.00 UTC</TD
1437 NAME="DATATYPE-DATETIME-OUTPUT"
1438 >8.5.2. Date/Time Output</A
1447 > The output format of the date/time types can be set to one of the four
1452 > (Ingres), traditional POSTGRES, and
1453 German, using the command <TT
1464 > standard requires the use of the ISO 8601
1465 format. The name of the <SPAN
1468 > output format is a
1469 historical accident.) <A
1470 HREF="datatype-datetime.html#DATATYPE-DATETIME-OUTPUT-TABLE"
1472 > shows examples of each
1473 output style. The output of the <TT
1480 > types is of course only the date or time part
1481 in accordance with the given examples.
1486 NAME="DATATYPE-DATETIME-OUTPUT-TABLE"
1490 >Table 8-14. Date/Time Output Styles</B
1495 ><COL><COL><COL><THEAD
1498 >Style Specification</TH
1510 >ISO 8601/SQL standard</TD
1512 >1997-12-17 07:37:16-08</TD
1518 >traditional style</TD
1520 >12/17/1997 07:37:16.00 PST</TD
1528 >Wed Dec 17 07:37:16 1997 PST</TD
1536 >17.12.1997 07:37:16.00 PST</TD
1545 > and POSTGRES styles, day appears before
1546 month if DMY field ordering has been specified, otherwise month appears
1549 HREF="datatype-datetime.html#DATATYPE-DATETIME-INPUT"
1552 for how this setting also affects interpretation of input values.)
1554 HREF="datatype-datetime.html#DATATYPE-DATETIME-OUTPUT2-TABLE"
1562 NAME="DATATYPE-DATETIME-OUTPUT2-TABLE"
1566 >Table 8-15. Date Order Conventions</B
1571 ><COL><COL><COL><THEAD
1603 >17/12/1997 15:37:16.00 CET</TD
1623 >12/17/1997 07:37:16.00 PST</TD
1643 >Wed 17 Dec 07:37:16 1997 PST</TD
1652 > output looks like the input format, except
1660 > are converted to years and days and that
1664 > is converted to an appropriate sign. In
1665 ISO mode the output looks like
1668 CLASS="PROGRAMLISTING"
1703 > The date/time styles can be selected by the user using the
1714 >postgresql.conf</TT
1715 > configuration file, or the
1719 > environment variable on the server or
1720 client. The formatting function <CODE
1725 HREF="functions-formatting.html"
1727 >) is also available as
1728 a more flexible way to format the date/time output.
1736 NAME="DATATYPE-TIMEZONES"
1737 >8.5.3. Time Zones</A
1743 > Time zones, and time-zone conventions, are influenced by
1744 political decisions, not just earth geometry. Time zones around the
1745 world became somewhat standardized during the 1900's,
1746 but continue to be prone to arbitrary changes.
1750 > uses your operating
1751 system's underlying features to provide output time-zone
1752 support, and these systems usually contain information for only
1753 the time period 1902 through 2038 (corresponding to the full
1754 range of conventional Unix system time).
1757 >timestamp with time zone</TT
1762 > will use time zone
1763 information only within that year range, and assume that times
1764 outside that range are in <ACRONYM
1768 But since time zone support is derived from the underlying operating
1769 system time-zone capabilities, it can handle daylight-saving time
1770 and other special behavior.
1776 > endeavors to be compatible with
1780 > standard definitions for typical usage.
1781 However, the <ACRONYM
1784 > standard has an odd mix of date and
1785 time types and capabilities. Two obvious problems are:
1796 does not have an associated time zone, the
1801 Time zones in the real world can have no meaning unless
1802 associated with a date as well as a time
1803 since the offset may vary through the year with daylight-saving
1809 > The default time zone is specified as a constant numeric offset
1813 >. It is not possible to adapt to daylight-saving
1814 time when doing date/time arithmetic across
1825 > To address these difficulties, we recommend using date/time types
1826 that contain both date and time when using time zones. We
1833 > using the type <TT
1837 > (though it is supported by
1841 > for legacy applications and
1842 for compatibility with other <ACRONYM
1846 implementations). <SPAN
1850 your local time zone for any type containing only date or time.
1853 > All dates and times are stored internally in
1857 >. Times are converted to local time
1858 on the database server before being sent to the client,
1859 hence by default are in the server time zone.
1862 > There are several ways to select the time zone used by the server:
1872 > environment variable on the server host
1873 is used by the server as the default time zone, if no other is
1882 > configuration parameter can be
1885 >postgresql.conf</TT
1894 > environment variable, if set at the
1895 client, is used by <SPAN
1899 applications to send a <TT
1903 command to the server upon connection.
1915 sets the time zone for the session.
1928 > If an invalid time zone is specified, the time zone becomes
1932 > (on most systems anyway).
1938 HREF="datetime-appendix.html"
1941 available time zones.
1949 NAME="DATATYPE-DATETIME-INTERNALS"
1950 >8.5.4. Internals</A
1957 for all date/time calculations. They have the nice property of correctly
1958 predicting/calculating any date more recent than 4713 BC
1959 to far into the future, using the assumption that the length of the
1960 year is 365.2425 days.
1963 > Date conventions before the 19th century make for interesting reading,
1964 but are not consistent enough to warrant coding into a date/time handler.
1973 SUMMARY="Footer navigation table"
1984 HREF="datatype-binary.html"
2002 HREF="datatype-boolean.html"
2012 >Binary Data Types</TD
2018 HREF="datatype.html"