PostgreSQL supports the full set of SQL date and time types, shown in Table 8.9. The operations available on these data types are described in Section 9.9. Dates are counted according to the Gregorian calendar, even in years before that calendar was introduced (see Section B.6 for more information).
Table 8.9. Date/Time Types
Name | Storage Size | Description | Low Value | High Value | Resolution |
---|---|---|---|---|---|
timestamp [ ( |
8 bytes | both date and time (no time zone) | 4713 BC | 294276 AD | 1 microsecond |
timestamp [ ( |
8 bytes | both date and time, with time zone | 4713 BC | 294276 AD | 1 microsecond |
date |
4 bytes | date (no time of day) | 4713 BC | 5874897 AD | 1 day |
time [ ( |
8 bytes | time of day (no date) | 00:00:00 | 24:00:00 | 1 microsecond |
time [ ( |
12 bytes | time of day (no date), with time zone | 00:00:00+1559 | 24:00:00-1559 | 1 microsecond |
interval [ |
16 bytes | time interval | -178000000 years | 178000000 years | 1 microsecond |
Note
The SQL standard requires that writing just timestamp
be equivalent to timestamp without time zone
, and PostgreSQL honors that behavior. timestamptz
is accepted as an abbreviation for timestamp with time zone
; this is a PostgreSQL extension.
time
, timestamp
, and interval
accept an optional precision value p
which specifies the number of fractional digits retained in the seconds field. By default, there is no explicit bound on precision. The allowed range of p
is from 0 to 6.
The interval
type has an additional option, which is to restrict the set of stored fields by writing one of these phrases:
YEAR MONTH DAY HOUR MINUTE SECOND YEAR TO MONTH DAY TO HOUR DAY TO MINUTE DAY TO SECOND HOUR TO MINUTE HOUR TO SECOND MINUTE TO SECOND
Note that if both fields
and p
are specified, the fields
must include SECOND
, since the precision applies only to the seconds.
The type time with time zone
is defined by the SQL standard, but the definition exhibits properties which lead to questionable usefulness. In most cases, a combination of date
, time
, timestamp without time zone
, and timestamp with time zone
should provide a complete range of date/time functionality required by any application.
8.5.1. Date/Time Input
Date and time input is accepted in almost any reasonable format, including ISO 8601, SQL-compatible, traditional POSTGRES, and others. For some formats, ordering of day, month, and year in date input is ambiguous and there is support for specifying the expected ordering of these fields. Set the DateStyle parameter to MDY
to select month-day-year interpretation, DMY
to select day-month-year interpretation, or YMD
to select year-month-day interpretation.
PostgreSQL is more flexible in handling date/time input than the SQL standard requires. See Appendix B for the exact parsing rules of date/time input and for the recognized text fields including months, days of the week, and time zones.
Remember that any date or time literal input needs to be enclosed in single quotes, like text strings. Refer to Section 4.1.2.7 for more information. SQL requires the following syntax
type
[ (p
) ] 'value
'
where p
is an optional precision specification giving the number of fractional digits in the seconds field. Precision can be specified for time
, timestamp
, and interval
types, and can range from 0 to 6. If no precision is specified in a constant specification, it defaults to the precision of the literal value (but not more than 6 digits).
8.5.1.1. Dates
Table 8.10 shows some possible inputs for the date
type.
Table 8.10. Date Input
Example | Description |
---|---|
1999-01-08 | ISO 8601; January 8 in any mode (recommended format) |
January 8, 1999 | unambiguous in any datestyle input mode |
1/8/1999 | January 8 in MDY mode; August 1 in DMY mode |
1/18/1999 | January 18 in MDY mode; rejected in other modes |
01/02/03 | January 2, 2003 in MDY mode; February 1, 2003 in DMY mode; February 3, 2001 in YMD mode |
1999-Jan-08 | January 8 in any mode |
Jan-08-1999 | January 8 in any mode |
08-Jan-1999 | January 8 in any mode |
99-Jan-08 | January 8 in YMD mode, else error |
08-Jan-99 | January 8, except error in YMD mode |
Jan-08-99 | January 8, except error in YMD mode |
19990108 | ISO 8601; January 8, 1999 in any mode |
990108 | ISO 8601; January 8, 1999 in any mode |
1999.008 | year and day of year |
J2451187 | Julian date |
January 8, 99 BC | year 99 BC |
8.5.1.2. Times
The time-of-day types are time [ (
and p
) ] without time zonetime [ (
. p
) ] with time zonetime
alone is equivalent to time without time zone
.
Valid input for these types consists of a time of day followed by an optional time zone. (See Table 8.11 and Table 8.12.) If a time zone is specified in the input for time without time zone
, it is silently ignored. You can also specify a date but it will be ignored, except when you use a time zone name that involves a daylight-savings rule, such as America/New_York
. In this case specifying the date is required in order to determine whether standard or daylight-savings time applies. The appropriate time zone offset is recorded in the time with time zone
value.
Table 8.11. Time Input
Example | Description |
---|---|
04:05:06.789 |
ISO 8601 |
04:05:06 |
ISO 8601 |
04:05 |
ISO 8601 |
040506 |
ISO 8601 |
04:05 AM |
same as 04:05; AM does not affect value |
04:05 PM |
same as 16:05; input hour must be <= 12 |
04:05:06.789-8 |
ISO 8601, with time zone as UTC offset |
04:05:06-08:00 |
ISO 8601, with time zone as UTC offset |
04:05-08:00 |
ISO 8601, with time zone as UTC offset |
040506-08 |
ISO 8601, with time zone as UTC offset |
040506+0730 |
ISO 8601, with fractional-hour time zone as UTC offset |
040506+07:30:00 |
UTC offset specified to seconds (not allowed in ISO 8601) |
04:05:06 PST |
time zone specified by abbreviation |
2003-04-12 04:05:06 America/New_York |
time zone specified by full name |
Table 8.12. Time Zone Input
Example | Description |
---|---|
PST |
Abbreviation (for Pacific Standard Time) |
America/New_York |
Full time zone name |
PST8PDT |
POSIX-style time zone specification |
-8:00:00 |
UTC offset for PST |
-8:00 |
UTC offset for PST (ISO 8601 extended format) |
-800 |
UTC offset for PST (ISO 8601 basic format) |
-8 |
UTC offset for PST (ISO 8601 basic format) |
zulu |
Military abbreviation for UTC |
z |
Short form of zulu (also in ISO 8601) |
Refer to Section 8.5.3 for more information on how to specify time zones.
8.5.1.3. Time Stamps
Valid input for the time stamp types consists of the concatenation of a date and a time, followed by an optional time zone, followed by an optional AD
or BC
. (Alternatively, AD
/BC
can appear before the time zone, but this is not the preferred ordering.) Thus:
1999-01-08 04:05:06
and:
1999-01-08 04:05:06 -8:00
are valid values, which follow the ISO 8601 standard. In addition, the common format:
January 8 04:05:06 1999 PST
is supported.
The SQL standard differentiates timestamp without time zone
and timestamp with time zone
literals by the presence of a “+” or “—” symbol and time zone offset after the time. Hence, according to the standard,
TIMESTAMP '2004-10-19 10:23:54'
is a timestamp without time zone
, while
TIMESTAMP '2004-10-19 10:23:54+02'
is a timestamp with time zone
. PostgreSQL never examines the content of a literal string before determining its type, and therefore will treat both of the above as timestamp without time zone
. To ensure that a literal is treated as timestamp with time zone
, give it the correct explicit type:
TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
In a literal that has been determined to be timestamp without time zone
, PostgreSQL will silently ignore any time zone indication. That is, the resulting value is derived from the date/time fields in the input value, and is not adjusted for time zone.
For timestamp with time zone
, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system’s TimeZone parameter, and is converted to UTC using the offset for the timezone
zone.
When a timestamp with time zone
value is output, it is always converted from UTC to the current timezone
zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone
or use the AT TIME ZONE
construct (see Section 9.9.4).
Conversions between timestamp without time zone
and timestamp with time zone
normally assume that the timestamp without time zone
value should be taken or given as timezone
local time. A different time zone can be specified for the conversion using AT TIME ZONE
.
8.5.1.4. Special Values
PostgreSQL supports several special date/time input values for convenience, as shown in Table 8.13. The values infinity
and -infinity
are specially represented inside the system and will be displayed unchanged; but the others are simply notational shorthands that will be converted to ordinary date/time values when read. (In particular, now
and related strings are converted to a specific time value as soon as they are read.) All of these values need to be enclosed in single quotes when used as constants in SQL commands.
Table 8.13. Special Date/Time Inputs
Input String | Valid Types | Description |
---|---|---|
epoch |
date , timestamp |
1970-01-01 00:00:00+00 (Unix system time zero) |
infinity |
date , timestamp |
later than all other time stamps |
-infinity |
date , timestamp |
earlier than all other time stamps |
now |
date , time , timestamp |
current transaction’s start time |
today |
date , timestamp |
midnight (00:00 ) today |
tomorrow |
date , timestamp |
midnight (00:00 ) tomorrow |
yesterday |
date , timestamp |
midnight (00:00 ) yesterday |
allballs |
time |
00:00:00.00 UTC |
The following SQL-compatible functions can also be used to obtain the current time value for the corresponding data type: CURRENT_DATE
, CURRENT_TIME
, CURRENT_TIMESTAMP
, LOCALTIME
, LOCALTIMESTAMP
. (See Section 9.9.5.) Note that these are SQL functions and are not recognized in data input strings.
Caution
While the input strings now
, today
, tomorrow
, and yesterday
are fine to use in interactive SQL commands, they can have surprising behavior when the command is saved to be executed later, for example in prepared statements, views, and function definitions. The string can be converted to a specific time value that continues to be used long after it becomes stale. Use one of the SQL functions instead in such contexts. For example, CURRENT_DATE + 1
is safer than 'tomorrow'::date
.
8.5.2. Date/Time Output
The output format of the date/time types can be set to one of the four styles ISO 8601, SQL (Ingres), traditional POSTGRES (Unix date format), or German. The default is the ISO format. (The SQL standard requires the use of the ISO 8601 format. The name of the “SQL” output format is a historical accident.) Table 8.14 shows examples of each output style. The output of the date
and time
types is generally only the date or time part in accordance with the given examples. However, the POSTGRES style outputs date-only values in ISO format.
Table 8.14. Date/Time Output Styles
Style Specification | Description | Example |
---|---|---|
ISO |
ISO 8601, SQL standard | 1997-12-17 07:37:16-08 |
SQL |
traditional style | 12/17/1997 07:37:16.00 PST |
Postgres |
original style | Wed Dec 17 07:37:16 1997 PST |
German |
regional style | 17.12.1997 07:37:16.00 PST |
Note
ISO 8601 specifies the use of uppercase letter T
to separate the date and time. PostgreSQL accepts that format on input, but on output it uses a space rather than T
, as shown above. This is for readability and for consistency with RFC 3339 as well as some other database systems.
In the SQL and POSTGRES styles, day appears before month if DMY field ordering has been specified, otherwise month appears before day. (See Section 8.5.1 for how this setting also affects interpretation of input values.) Table 8.15 shows examples.
Table 8.15. Date Order Conventions
datestyle Setting |
Input Ordering | Example Output |
---|---|---|
SQL, DMY |
day /month /year |
17/12/1997 15:37:16.00 CET |
SQL, MDY |
month /day /year |
12/17/1997 07:37:16.00 PST |
Postgres, DMY |
day /month /year |
Wed 17 Dec 07:37:16 1997 PST |
In the ISO style, the time zone is always shown as a signed numeric offset from UTC, with positive sign used for zones east of Greenwich. The offset will be shown as hh
(hours only) if it is an integral number of hours, else as hh
:mm
if it is an integral number of minutes, else as hh
:mm
:ss
. (The third case is not possible with any modern time zone standard, but it can appear when working with timestamps that predate the adoption of standardized time zones.) In the other date styles, the time zone is shown as an alphabetic abbreviation if one is in common use in the current zone. Otherwise it appears as a signed numeric offset in ISO 8601 basic format (hh
or hhmm
).
The date/time style can be selected by the user using the SET datestyle
command, the DateStyle parameter in the postgresql.conf
configuration file, or the PGDATESTYLE
environment variable on the server or client.
The formatting function to_char
(see Section 9.8) is also available as a more flexible way to format date/time output.
8.5.3. Time Zones
Time zones, and time-zone conventions, are influenced by political decisions, not just earth geometry. Time zones around the world became somewhat standardized during the 1900s, but continue to be prone to arbitrary changes, particularly with respect to daylight-savings rules. PostgreSQL uses the widely-used IANA (Olson) time zone database for information about historical time zone rules. For times in the future, the assumption is that the latest known rules for a given time zone will continue to be observed indefinitely far into the future.
PostgreSQL endeavors to be compatible with the SQL standard definitions for typical usage. However, the SQL standard has an odd mix of date and time types and capabilities. Two obvious problems are:
-
Although the
date
type cannot have an associated time zone, thetime
type can. Time zones in the real world have little meaning unless associated with a date as well as a time, since the offset can vary through the year with daylight-saving time boundaries. -
The default time zone is specified as a constant numeric offset from UTC. It is therefore impossible to adapt to daylight-saving time when doing date/time arithmetic across DST boundaries.
To address these difficulties, we recommend using date/time types that contain both date and time when using time zones. We do not recommend using the type time with time zone
(though it is supported by PostgreSQL for legacy applications and for compliance with the SQL standard). PostgreSQL assumes your local time zone for any type containing only date or time.
All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client.
PostgreSQL allows you to specify time zones in three different forms:
-
A full time zone name, for example
America/New_York
. The recognized time zone names are listed in thepg_timezone_names
view (see Section 54.32). PostgreSQL uses the widely-used IANA time zone data for this purpose, so the same time zone names are also recognized by other software. -
A time zone abbreviation, for example
PST
. Such a specification merely defines a particular offset from UTC, in contrast to full time zone names which can imply a set of daylight savings transition rules as well. The recognized abbreviations are listed in thepg_timezone_abbrevs
view (see Section 54.31). You cannot set the configuration parameters TimeZone or log_timezone to a time zone abbreviation, but you can use abbreviations in date/time input values and with theAT TIME ZONE
operator. -
In addition to the timezone names and abbreviations, PostgreSQL will accept POSIX-style time zone specifications, as described in Section B.5. This option is not normally preferable to using a named time zone, but it may be necessary if no suitable IANA time zone entry is available.
In short, this is the difference between abbreviations and full names: abbreviations represent a specific offset from UTC, whereas many of the full names imply a local daylight-savings time rule, and so have two possible UTC offsets. As an example, 2014-06-04 12:00 America/New_York
represents noon local time in New York, which for this particular date was Eastern Daylight Time (UTC-4). So 2014-06-04 12:00 EDT
specifies that same time instant. But 2014-06-04 12:00 EST
specifies noon Eastern Standard Time (UTC-5), regardless of whether daylight savings was nominally in effect on that date.
To complicate matters, some jurisdictions have used the same timezone abbreviation to mean different UTC offsets at different times; for example, in Moscow MSK
has meant UTC+3 in some years and UTC+4 in others. PostgreSQL interprets such abbreviations according to whatever they meant (or had most recently meant) on the specified date; but, as with the EST
example above, this is not necessarily the same as local civil time on that date.
In all cases, timezone names and abbreviations are recognized case-insensitively. (This is a change from PostgreSQL versions prior to 8.2, which were case-sensitive in some contexts but not others.)
Neither timezone names nor abbreviations are hard-wired into the server; they are obtained from configuration files stored under .../share/timezone/
and .../share/timezonesets/
of the installation directory (see Section B.4).
The TimeZone configuration parameter can be set in the file postgresql.conf
, or in any of the other standard ways described in Chapter 20. There are also some special ways to set it:
-
The SQL command
SET TIME ZONE
sets the time zone for the session. This is an alternative spelling ofSET TIMEZONE TO
with a more SQL-spec-compatible syntax. -
The
PGTZ
environment variable is used by libpq clients to send aSET TIME ZONE
command to the server upon connection.
8.5.4. Interval Input
interval
values can be written using the following verbose syntax:
[@]quantity
unit
[quantity
unit
...] [direction
]
where quantity
is a number (possibly signed); unit
is microsecond
, millisecond
, second
, minute
, hour
, day
, week
, month
, year
, decade
, century
, millennium
, or abbreviations or plurals of these units; direction
can be ago
or empty. The at sign (@
) is optional noise. The amounts of the different units are implicitly added with appropriate sign accounting. ago
negates all the fields. This syntax is also used for interval output, if IntervalStyle is set to postgres_verbose
.
Quantities of days, hours, minutes, and seconds can be specified without explicit unit markings. For example, '1 12:59:10'
is read the same as '1 day 12 hours 59 min 10 sec'
. Also, a combination of years and months can be specified with a dash; for example '200-10'
is read the same as '200 years 10 months'
. (These shorter forms are in fact the only ones allowed by the SQL standard, and are used for output when IntervalStyle
is set to sql_standard
.)
Interval values can also be written as ISO 8601 time intervals, using either the “format with designators” of the standard’s section 4.4.3.2 or the “alternative format” of section 4.4.3.3. The format with designators looks like this:
Pquantity
unit
[quantity
unit
...] [ T [quantity
unit
...]]
The string must start with a P
, and may include a T
that introduces the time-of-day units. The available unit abbreviations are given in Table 8.16. Units may be omitted, and may be specified in any order, but units smaller than a day must appear after T
. In particular, the meaning of M
depends on whether it is before or after T
.
Table 8.16. ISO 8601 Interval Unit Abbreviations
Abbreviation | Meaning |
---|---|
Y | Years |
M | Months (in the date part) |
W | Weeks |
D | Days |
H | Hours |
M | Minutes (in the time part) |
S | Seconds |
In the alternative format:
P [years
-months
-days
] [ Thours
:minutes
:seconds
]
the string must begin with P
, and a T
separates the date and time parts of the interval. The values are given as numbers similar to ISO 8601 dates.
When writing an interval constant with a fields
specification, or when assigning a string to an interval column that was defined with a fields
specification, the interpretation of unmarked quantities depends on the fields
. For example INTERVAL '1' YEAR
is read as 1 year, whereas INTERVAL '1'
means 1 second. Also, field values “to the right” of the least significant field allowed by the fields
specification are silently discarded. For example, writing INTERVAL '1 day 2:03:04' HOUR TO MINUTE
results in dropping the seconds field, but not the day field.
According to the SQL standard all fields of an interval value must have the same sign, so a leading negative sign applies to all fields; for example the negative sign in the interval literal '-1 2:03:04'
applies to both the days and hour/minute/second parts. PostgreSQL allows the fields to have different signs, and traditionally treats each field in the textual representation as independently signed, so that the hour/minute/second part is considered positive in this example. If IntervalStyle
is set to sql_standard
then a leading sign is considered to apply to all fields (but only if no additional signs appear). Otherwise the traditional PostgreSQL interpretation is used. To avoid ambiguity, it’s recommended to attach an explicit sign to each field if any field is negative.
Field values can have fractional parts: for example, '1.5 weeks'
or '01:02:03.45'
. However, because interval internally stores only three integer units (months, days, microseconds), fractional units must be spilled to smaller units. Fractional parts of units greater than months are rounded to be an integer number of months, e.g. '1.5 years'
becomes '1 year 6 mons'
. Fractional parts of weeks and days are computed to be an integer number of days and microseconds, assuming 30 days per month and 24 hours per day, e.g., '1.75 months'
becomes 1 mon 22 days 12:00:00
. Only seconds will ever be shown as fractional on output.
Table 8.17 shows some examples of valid interval
input.
Table 8.17. Interval Input
Example | Description |
---|---|
1-2 |
SQL standard format: 1 year 2 months |
3 4:05:06 |
SQL standard format: 3 days 4 hours 5 minutes 6 seconds |
1 year 2 months 3 days 4 hours 5 minutes 6 seconds |
Traditional Postgres format: 1 year 2 months 3 days 4 hours 5 minutes 6 seconds |
P1Y2M3DT4H5M6S |
ISO 8601 “format with designators”: same meaning as above |
P0001-02-03T04:05:06 |
ISO 8601 “alternative format”: same meaning as above |
Internally interval
values are stored as months, days, and microseconds. This is done because the number of days in a month varies, and a day can have 23 or 25 hours if a daylight savings time adjustment is involved. The months and days fields are integers while the microseconds field can store fractional seconds. Because intervals are usually created from constant strings or timestamp
subtraction, this storage method works well in most cases, but can cause unexpected results:
SELECT EXTRACT(hours from '80 minutes'::interval); date_part ----------- 1 SELECT EXTRACT(days from '80 hours'::interval); date_part ----------- 0
Functions justify_days
and justify_hours
are available for adjusting days and hours that overflow their normal ranges.
8.5.5. Interval Output
The output format of the interval type can be set to one of the four styles sql_standard
, postgres
, postgres_verbose
, or iso_8601
, using the command SET intervalstyle
. The default is the postgres
format. Table 8.18 shows examples of each output style.
The sql_standard
style produces output that conforms to the SQL standard’s specification for interval literal strings, if the interval value meets the standard’s restrictions (either year-month only or day-time only, with no mixing of positive and negative components). Otherwise the output looks like a standard year-month literal string followed by a day-time literal string, with explicit signs added to disambiguate mixed-sign intervals.
The output of the postgres
style matches the output of PostgreSQL releases prior to 8.4 when the DateStyle parameter was set to ISO
.
The output of the postgres_verbose
style matches the output of PostgreSQL releases prior to 8.4 when the DateStyle
parameter was set to non-ISO
output.
The output of the iso_8601
style matches the “format with designators” described in section 4.4.3.2 of the ISO 8601 standard.
Table 8.18. Interval Output Style Examples
Style Specification | Year-Month Interval | Day-Time Interval | Mixed Interval |
---|---|---|---|
sql_standard |
1-2 | 3 4:05:06 | -1-2 +3 -4:05:06 |
postgres |
1 year 2 mons | 3 days 04:05:06 | -1 year -2 mons +3 days -04:05:06 |
postgres_verbose |
@ 1 year 2 mons | @ 3 days 4 hours 5 mins 6 secs | @ 1 year 2 mons -3 days 4 hours 5 mins 6 secs ago |
iso_8601 |
P1Y2M | P3DT4H5M6S | P-1Y-2M3DT-4H-5M-6S |
PostgreSQL supports the full set of SQL date and time types, shown in Table 8.9. The operations available on these data types are described in Section 9.9. Dates are counted according to the Gregorian calendar, even in years before that calendar was introduced (see Section B.6 for more information).
Table 8.9. Date/Time Types
Name | Storage Size | Description | Low Value | High Value | Resolution |
---|---|---|---|---|---|
timestamp [ ( |
8 bytes | both date and time (no time zone) | 4713 BC | 294276 AD | 1 microsecond |
timestamp [ ( |
8 bytes | both date and time, with time zone | 4713 BC | 294276 AD | 1 microsecond |
date |
4 bytes | date (no time of day) | 4713 BC | 5874897 AD | 1 day |
time [ ( |
8 bytes | time of day (no date) | 00:00:00 | 24:00:00 | 1 microsecond |
time [ ( |
12 bytes | time of day (no date), with time zone | 00:00:00+1559 | 24:00:00-1559 | 1 microsecond |
interval [ |
16 bytes | time interval | -178000000 years | 178000000 years | 1 microsecond |
Note
The SQL standard requires that writing just timestamp
be equivalent to timestamp without time zone
, and PostgreSQL honors that behavior. timestamptz
is accepted as an abbreviation for timestamp with time zone
; this is a PostgreSQL extension.
time
, timestamp
, and interval
accept an optional precision value p
which specifies the number of fractional digits retained in the seconds field. By default, there is no explicit bound on precision. The allowed range of p
is from 0 to 6.
The interval
type has an additional option, which is to restrict the set of stored fields by writing one of these phrases:
YEAR MONTH DAY HOUR MINUTE SECOND YEAR TO MONTH DAY TO HOUR DAY TO MINUTE DAY TO SECOND HOUR TO MINUTE HOUR TO SECOND MINUTE TO SECOND
Note that if both fields
and p
are specified, the fields
must include SECOND
, since the precision applies only to the seconds.
The type time with time zone
is defined by the SQL standard, but the definition exhibits properties which lead to questionable usefulness. In most cases, a combination of date
, time
, timestamp without time zone
, and timestamp with time zone
should provide a complete range of date/time functionality required by any application.
8.5.1. Date/Time Input
Date and time input is accepted in almost any reasonable format, including ISO 8601, SQL-compatible, traditional POSTGRES, and others. For some formats, ordering of day, month, and year in date input is ambiguous and there is support for specifying the expected ordering of these fields. Set the DateStyle parameter to MDY
to select month-day-year interpretation, DMY
to select day-month-year interpretation, or YMD
to select year-month-day interpretation.
PostgreSQL is more flexible in handling date/time input than the SQL standard requires. See Appendix B for the exact parsing rules of date/time input and for the recognized text fields including months, days of the week, and time zones.
Remember that any date or time literal input needs to be enclosed in single quotes, like text strings. Refer to Section 4.1.2.7 for more information. SQL requires the following syntax
type
[ (p
) ] 'value
'
where p
is an optional precision specification giving the number of fractional digits in the seconds field. Precision can be specified for time
, timestamp
, and interval
types, and can range from 0 to 6. If no precision is specified in a constant specification, it defaults to the precision of the literal value (but not more than 6 digits).
8.5.1.1. Dates
Table 8.10 shows some possible inputs for the date
type.
Table 8.10. Date Input
Example | Description |
---|---|
1999-01-08 | ISO 8601; January 8 in any mode (recommended format) |
January 8, 1999 | unambiguous in any datestyle input mode |
1/8/1999 | January 8 in MDY mode; August 1 in DMY mode |
1/18/1999 | January 18 in MDY mode; rejected in other modes |
01/02/03 | January 2, 2003 in MDY mode; February 1, 2003 in DMY mode; February 3, 2001 in YMD mode |
1999-Jan-08 | January 8 in any mode |
Jan-08-1999 | January 8 in any mode |
08-Jan-1999 | January 8 in any mode |
99-Jan-08 | January 8 in YMD mode, else error |
08-Jan-99 | January 8, except error in YMD mode |
Jan-08-99 | January 8, except error in YMD mode |
19990108 | ISO 8601; January 8, 1999 in any mode |
990108 | ISO 8601; January 8, 1999 in any mode |
1999.008 | year and day of year |
J2451187 | Julian date |
January 8, 99 BC | year 99 BC |
8.5.1.2. Times
The time-of-day types are time [ (
and p
) ] without time zonetime [ (
. p
) ] with time zonetime
alone is equivalent to time without time zone
.
Valid input for these types consists of a time of day followed by an optional time zone. (See Table 8.11 and Table 8.12.) If a time zone is specified in the input for time without time zone
, it is silently ignored. You can also specify a date but it will be ignored, except when you use a time zone name that involves a daylight-savings rule, such as America/New_York
. In this case specifying the date is required in order to determine whether standard or daylight-savings time applies. The appropriate time zone offset is recorded in the time with time zone
value.
Table 8.11. Time Input
Example | Description |
---|---|
04:05:06.789 |
ISO 8601 |
04:05:06 |
ISO 8601 |
04:05 |
ISO 8601 |
040506 |
ISO 8601 |
04:05 AM |
same as 04:05; AM does not affect value |
04:05 PM |
same as 16:05; input hour must be <= 12 |
04:05:06.789-8 |
ISO 8601, with time zone as UTC offset |
04:05:06-08:00 |
ISO 8601, with time zone as UTC offset |
04:05-08:00 |
ISO 8601, with time zone as UTC offset |
040506-08 |
ISO 8601, with time zone as UTC offset |
040506+0730 |
ISO 8601, with fractional-hour time zone as UTC offset |
040506+07:30:00 |
UTC offset specified to seconds (not allowed in ISO 8601) |
04:05:06 PST |
time zone specified by abbreviation |
2003-04-12 04:05:06 America/New_York |
time zone specified by full name |
Table 8.12. Time Zone Input
Example | Description |
---|---|
PST |
Abbreviation (for Pacific Standard Time) |
America/New_York |
Full time zone name |
PST8PDT |
POSIX-style time zone specification |
-8:00:00 |
UTC offset for PST |
-8:00 |
UTC offset for PST (ISO 8601 extended format) |
-800 |
UTC offset for PST (ISO 8601 basic format) |
-8 |
UTC offset for PST (ISO 8601 basic format) |
zulu |
Military abbreviation for UTC |
z |
Short form of zulu (also in ISO 8601) |
Refer to Section 8.5.3 for more information on how to specify time zones.
8.5.1.3. Time Stamps
Valid input for the time stamp types consists of the concatenation of a date and a time, followed by an optional time zone, followed by an optional AD
or BC
. (Alternatively, AD
/BC
can appear before the time zone, but this is not the preferred ordering.) Thus:
1999-01-08 04:05:06
and:
1999-01-08 04:05:06 -8:00
are valid values, which follow the ISO 8601 standard. In addition, the common format:
January 8 04:05:06 1999 PST
is supported.
The SQL standard differentiates timestamp without time zone
and timestamp with time zone
literals by the presence of a “+” or “—” symbol and time zone offset after the time. Hence, according to the standard,
TIMESTAMP '2004-10-19 10:23:54'
is a timestamp without time zone
, while
TIMESTAMP '2004-10-19 10:23:54+02'
is a timestamp with time zone
. PostgreSQL never examines the content of a literal string before determining its type, and therefore will treat both of the above as timestamp without time zone
. To ensure that a literal is treated as timestamp with time zone
, give it the correct explicit type:
TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
In a literal that has been determined to be timestamp without time zone
, PostgreSQL will silently ignore any time zone indication. That is, the resulting value is derived from the date/time fields in the input value, and is not adjusted for time zone.
For timestamp with time zone
, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system’s TimeZone parameter, and is converted to UTC using the offset for the timezone
zone.
When a timestamp with time zone
value is output, it is always converted from UTC to the current timezone
zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone
or use the AT TIME ZONE
construct (see Section 9.9.3).
Conversions between timestamp without time zone
and timestamp with time zone
normally assume that the timestamp without time zone
value should be taken or given as timezone
local time. A different time zone can be specified for the conversion using AT TIME ZONE
.
8.5.1.4. Special Values
PostgreSQL supports several special date/time input values for convenience, as shown in Table 8.13. The values infinity
and -infinity
are specially represented inside the system and will be displayed unchanged; but the others are simply notational shorthands that will be converted to ordinary date/time values when read. (In particular, now
and related strings are converted to a specific time value as soon as they are read.) All of these values need to be enclosed in single quotes when used as constants in SQL commands.
Table 8.13. Special Date/Time Inputs
Input String | Valid Types | Description |
---|---|---|
epoch |
date , timestamp |
1970-01-01 00:00:00+00 (Unix system time zero) |
infinity |
date , timestamp |
later than all other time stamps |
-infinity |
date , timestamp |
earlier than all other time stamps |
now |
date , time , timestamp |
current transaction’s start time |
today |
date , timestamp |
midnight (00:00 ) today |
tomorrow |
date , timestamp |
midnight (00:00 ) tomorrow |
yesterday |
date , timestamp |
midnight (00:00 ) yesterday |
allballs |
time |
00:00:00.00 UTC |
The following SQL-compatible functions can also be used to obtain the current time value for the corresponding data type: CURRENT_DATE
, CURRENT_TIME
, CURRENT_TIMESTAMP
, LOCALTIME
, LOCALTIMESTAMP
. (See Section 9.9.4.) Note that these are SQL functions and are not recognized in data input strings.
Caution
While the input strings now
, today
, tomorrow
, and yesterday
are fine to use in interactive SQL commands, they can have surprising behavior when the command is saved to be executed later, for example in prepared statements, views, and function definitions. The string can be converted to a specific time value that continues to be used long after it becomes stale. Use one of the SQL functions instead in such contexts. For example, CURRENT_DATE + 1
is safer than 'tomorrow'::date
.
8.5.2. Date/Time Output
The output format of the date/time types can be set to one of the four styles ISO 8601, SQL (Ingres), traditional POSTGRES (Unix date format), or German. The default is the ISO format. (The SQL standard requires the use of the ISO 8601 format. The name of the “SQL” output format is a historical accident.) Table 8.14 shows examples of each output style. The output of the date
and time
types is generally only the date or time part in accordance with the given examples. However, the POSTGRES style outputs date-only values in ISO format.
Table 8.14. Date/Time Output Styles
Style Specification | Description | Example |
---|---|---|
ISO |
ISO 8601, SQL standard | 1997-12-17 07:37:16-08 |
SQL |
traditional style | 12/17/1997 07:37:16.00 PST |
Postgres |
original style | Wed Dec 17 07:37:16 1997 PST |
German |
regional style | 17.12.1997 07:37:16.00 PST |
Note
ISO 8601 specifies the use of uppercase letter T
to separate the date and time. PostgreSQL accepts that format on input, but on output it uses a space rather than T
, as shown above. This is for readability and for consistency with RFC 3339 as well as some other database systems.
In the SQL and POSTGRES styles, day appears before month if DMY field ordering has been specified, otherwise month appears before day. (See Section 8.5.1 for how this setting also affects interpretation of input values.) Table 8.15 shows examples.
Table 8.15. Date Order Conventions
datestyle Setting |
Input Ordering | Example Output |
---|---|---|
SQL, DMY |
day /month /year |
17/12/1997 15:37:16.00 CET |
SQL, MDY |
month /day /year |
12/17/1997 07:37:16.00 PST |
Postgres, DMY |
day /month /year |
Wed 17 Dec 07:37:16 1997 PST |
In the ISO style, the time zone is always shown as a signed numeric offset from UTC, with positive sign used for zones east of Greenwich. The offset will be shown as hh
(hours only) if it is an integral number of hours, else as hh
:mm
if it is an integral number of minutes, else as hh
:mm
:ss
. (The third case is not possible with any modern time zone standard, but it can appear when working with timestamps that predate the adoption of standardized time zones.) In the other date styles, the time zone is shown as an alphabetic abbreviation if one is in common use in the current zone. Otherwise it appears as a signed numeric offset in ISO 8601 basic format (hh
or hhmm
).
The date/time style can be selected by the user using the SET datestyle
command, the DateStyle parameter in the postgresql.conf
configuration file, or the PGDATESTYLE
environment variable on the server or client.
The formatting function to_char
(see Section 9.8) is also available as a more flexible way to format date/time output.
8.5.3. Time Zones
Time zones, and time-zone conventions, are influenced by political decisions, not just earth geometry. Time zones around the world became somewhat standardized during the 1900s, but continue to be prone to arbitrary changes, particularly with respect to daylight-savings rules. PostgreSQL uses the widely-used IANA (Olson) time zone database for information about historical time zone rules. For times in the future, the assumption is that the latest known rules for a given time zone will continue to be observed indefinitely far into the future.
PostgreSQL endeavors to be compatible with the SQL standard definitions for typical usage. However, the SQL standard has an odd mix of date and time types and capabilities. Two obvious problems are:
-
Although the
date
type cannot have an associated time zone, thetime
type can. Time zones in the real world have little meaning unless associated with a date as well as a time, since the offset can vary through the year with daylight-saving time boundaries. -
The default time zone is specified as a constant numeric offset from UTC. It is therefore impossible to adapt to daylight-saving time when doing date/time arithmetic across DST boundaries.
To address these difficulties, we recommend using date/time types that contain both date and time when using time zones. We do not recommend using the type time with time zone
(though it is supported by PostgreSQL for legacy applications and for compliance with the SQL standard). PostgreSQL assumes your local time zone for any type containing only date or time.
All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client.
PostgreSQL allows you to specify time zones in three different forms:
-
A full time zone name, for example
America/New_York
. The recognized time zone names are listed in thepg_timezone_names
view (see Section 51.92). PostgreSQL uses the widely-used IANA time zone data for this purpose, so the same time zone names are also recognized by other software. -
A time zone abbreviation, for example
PST
. Such a specification merely defines a particular offset from UTC, in contrast to full time zone names which can imply a set of daylight savings transition rules as well. The recognized abbreviations are listed in thepg_timezone_abbrevs
view (see Section 51.91). You cannot set the configuration parameters TimeZone or log_timezone to a time zone abbreviation, but you can use abbreviations in date/time input values and with theAT TIME ZONE
operator. -
In addition to the timezone names and abbreviations, PostgreSQL will accept POSIX-style time zone specifications, as described in Section B.5. This option is not normally preferable to using a named time zone, but it may be necessary if no suitable IANA time zone entry is available.
In short, this is the difference between abbreviations and full names: abbreviations represent a specific offset from UTC, whereas many of the full names imply a local daylight-savings time rule, and so have two possible UTC offsets. As an example, 2014-06-04 12:00 America/New_York
represents noon local time in New York, which for this particular date was Eastern Daylight Time (UTC-4). So 2014-06-04 12:00 EDT
specifies that same time instant. But 2014-06-04 12:00 EST
specifies noon Eastern Standard Time (UTC-5), regardless of whether daylight savings was nominally in effect on that date.
To complicate matters, some jurisdictions have used the same timezone abbreviation to mean different UTC offsets at different times; for example, in Moscow MSK
has meant UTC+3 in some years and UTC+4 in others. PostgreSQL interprets such abbreviations according to whatever they meant (or had most recently meant) on the specified date; but, as with the EST
example above, this is not necessarily the same as local civil time on that date.
In all cases, timezone names and abbreviations are recognized case-insensitively. (This is a change from PostgreSQL versions prior to 8.2, which were case-sensitive in some contexts but not others.)
Neither timezone names nor abbreviations are hard-wired into the server; they are obtained from configuration files stored under .../share/timezone/
and .../share/timezonesets/
of the installation directory (see Section B.4).
The TimeZone configuration parameter can be set in the file postgresql.conf
, or in any of the other standard ways described in Chapter 19. There are also some special ways to set it:
-
The SQL command
SET TIME ZONE
sets the time zone for the session. This is an alternative spelling ofSET TIMEZONE TO
with a more SQL-spec-compatible syntax. -
The
PGTZ
environment variable is used by libpq clients to send aSET TIME ZONE
command to the server upon connection.
8.5.4. Interval Input
interval
values can be written using the following verbose syntax:
[@]quantity
unit
[quantity
unit
...] [direction
]
where quantity
is a number (possibly signed); unit
is microsecond
, millisecond
, second
, minute
, hour
, day
, week
, month
, year
, decade
, century
, millennium
, or abbreviations or plurals of these units; direction
can be ago
or empty. The at sign (@
) is optional noise. The amounts of the different units are implicitly added with appropriate sign accounting. ago
negates all the fields. This syntax is also used for interval output, if IntervalStyle is set to postgres_verbose
.
Quantities of days, hours, minutes, and seconds can be specified without explicit unit markings. For example, '1 12:59:10'
is read the same as '1 day 12 hours 59 min 10 sec'
. Also, a combination of years and months can be specified with a dash; for example '200-10'
is read the same as '200 years 10 months'
. (These shorter forms are in fact the only ones allowed by the SQL standard, and are used for output when IntervalStyle
is set to sql_standard
.)
Interval values can also be written as ISO 8601 time intervals, using either the “format with designators” of the standard’s section 4.4.3.2 or the “alternative format” of section 4.4.3.3. The format with designators looks like this:
Pquantity
unit
[quantity
unit
...] [ T [quantity
unit
...]]
The string must start with a P
, and may include a T
that introduces the time-of-day units. The available unit abbreviations are given in Table 8.16. Units may be omitted, and may be specified in any order, but units smaller than a day must appear after T
. In particular, the meaning of M
depends on whether it is before or after T
.
Table 8.16. ISO 8601 Interval Unit Abbreviations
Abbreviation | Meaning |
---|---|
Y | Years |
M | Months (in the date part) |
W | Weeks |
D | Days |
H | Hours |
M | Minutes (in the time part) |
S | Seconds |
In the alternative format:
P [years
-months
-days
] [ Thours
:minutes
:seconds
]
the string must begin with P
, and a T
separates the date and time parts of the interval. The values are given as numbers similar to ISO 8601 dates.
When writing an interval constant with a fields
specification, or when assigning a string to an interval column that was defined with a fields
specification, the interpretation of unmarked quantities depends on the fields
. For example INTERVAL '1' YEAR
is read as 1 year, whereas INTERVAL '1'
means 1 second. Also, field values “to the right” of the least significant field allowed by the fields
specification are silently discarded. For example, writing INTERVAL '1 day 2:03:04' HOUR TO MINUTE
results in dropping the seconds field, but not the day field.
According to the SQL standard all fields of an interval value must have the same sign, so a leading negative sign applies to all fields; for example the negative sign in the interval literal '-1 2:03:04'
applies to both the days and hour/minute/second parts. PostgreSQL allows the fields to have different signs, and traditionally treats each field in the textual representation as independently signed, so that the hour/minute/second part is considered positive in this example. If IntervalStyle
is set to sql_standard
then a leading sign is considered to apply to all fields (but only if no additional signs appear). Otherwise the traditional PostgreSQL interpretation is used. To avoid ambiguity, it’s recommended to attach an explicit sign to each field if any field is negative.
Field values can have fractional parts: for example, '1.5 weeks'
or '01:02:03.45'
. However, because interval internally stores only three integer units (months, days, microseconds), fractional units must be spilled to smaller units. Fractional parts of units greater than months are truncated to be an integer number of months, e.g. '1.5 years'
becomes '1 year 6 mons'
. Fractional parts of weeks and days are computed to be an integer number of days and microseconds, assuming 30 days per month and 24 hours per day, e.g., '1.75 months'
becomes 1 mon 22 days 12:00:00
. Only seconds will ever be shown as fractional on output.
Table 8.17 shows some examples of valid interval
input.
Table 8.17. Interval Input
Example | Description |
---|---|
1-2 |
SQL standard format: 1 year 2 months |
3 4:05:06 |
SQL standard format: 3 days 4 hours 5 minutes 6 seconds |
1 year 2 months 3 days 4 hours 5 minutes 6 seconds |
Traditional Postgres format: 1 year 2 months 3 days 4 hours 5 minutes 6 seconds |
P1Y2M3DT4H5M6S |
ISO 8601 “format with designators”: same meaning as above |
P0001-02-03T04:05:06 |
ISO 8601 “alternative format”: same meaning as above |
Internally interval
values are stored as months, days, and microseconds. This is done because the number of days in a month varies, and a day can have 23 or 25 hours if a daylight savings time adjustment is involved. The months and days fields are integers while the microseconds field can store fractional seconds. Because intervals are usually created from constant strings or timestamp
subtraction, this storage method works well in most cases, but can cause unexpected results:
SELECT EXTRACT(hours from '80 minutes'::interval); date_part ----------- 1 SELECT EXTRACT(days from '80 hours'::interval); date_part ----------- 0
Functions justify_days
and justify_hours
are available for adjusting days and hours that overflow their normal ranges.
8.5.5. Interval Output
The output format of the interval type can be set to one of the four styles sql_standard
, postgres
, postgres_verbose
, or iso_8601
, using the command SET intervalstyle
. The default is the postgres
format. Table 8.18 shows examples of each output style.
The sql_standard
style produces output that conforms to the SQL standard’s specification for interval literal strings, if the interval value meets the standard’s restrictions (either year-month only or day-time only, with no mixing of positive and negative components). Otherwise the output looks like a standard year-month literal string followed by a day-time literal string, with explicit signs added to disambiguate mixed-sign intervals.
The output of the postgres
style matches the output of PostgreSQL releases prior to 8.4 when the DateStyle parameter was set to ISO
.
The output of the postgres_verbose
style matches the output of PostgreSQL releases prior to 8.4 when the DateStyle
parameter was set to non-ISO
output.
The output of the iso_8601
style matches the “format with designators” described in section 4.4.3.2 of the ISO 8601 standard.
Table 8.18. Interval Output Style Examples
Style Specification | Year-Month Interval | Day-Time Interval | Mixed Interval |
---|---|---|---|
sql_standard |
1-2 | 3 4:05:06 | -1-2 +3 -4:05:06 |
postgres |
1 year 2 mons | 3 days 04:05:06 | -1 year -2 mons +3 days -04:05:06 |
postgres_verbose |
@ 1 year 2 mons | @ 3 days 4 hours 5 mins 6 secs | @ 1 year 2 mons -3 days 4 hours 5 mins 6 secs ago |
iso_8601 |
P1Y2M | P3DT4H5M6S | P-1Y-2M3DT-4H-5M-6S |
PostgreSQL поддерживает полный набор типов даты и времени SQL, показанный в Таблице 8.9 . Операции, доступные для этих типов данных, описаны в Разделе 9.9 . Даты считаются по григорианскому календарю, даже за годы до того, как этот календарь был введен ( дополнительную информацию см. В разделе B.6 ).
time
, timestamp
и interval
принимают необязательное значение точности p
, которое указывает количество дробных цифр, сохраняемых в поле секунд. По умолчанию нет явных ограничений на точность. Допустимый диапазон p
от 0 до 6.
У типа interval
есть дополнительная опция, которая заключается в ограничении набора сохраняемых полей, написав одну из этих фраз:
Обратите внимание, что если указаны оба fields
и p
, fields
должны включать SECOND
, поскольку точность применяется только к секундам.
Тип time with time zone
определяется стандартом SQL, но определение демонстрирует свойства, которые приводят к сомнительной полезности. В большинстве случаев комбинация date
, time
, timestamp without time zone
и timestamp with time zone
должна обеспечивать полный диапазон функций даты и времени, необходимых для любого приложения.
8.5.1 Дата/время входа
Ввод даты и времени принимается практически в любом приемлемом формате, включая ISO 8601, SQL-совместимый, традиционный POSTGRES и другие. Для некоторых форматов порядок дня, месяца и года при вводе даты неоднозначен, и поддерживается указание ожидаемого порядка этих полей. Установите для параметра DateStyle значение MDY MDY
чтобы выбрать интерпретацию месяц-день-год, DMY
, чтобы выбрать интерпретацию день-месяц-год, или YMD
, чтобы выбрать интерпретацию год-месяц-день.
PostgreSQL более гибок в обработке ввода даты/времени, чем требует стандарт SQL. См. Приложение B для точных правил синтаксического анализа ввода даты/времени и для распознаваемых текстовых полей, включая месяцы, дни недели и часовые пояса.
Помните, что любой вводимый литерал даты или времени должен быть заключен в одинарные кавычки, как текстовые строки. Обратитесь к Разделу 4.1.2.7 для получения дополнительной информации. SQL требует следующего синтаксиса
type [ (p) ] 'value'
где p
— необязательная спецификация точности, дающая количество цифр дробной части в поле секунд. Точность может быть указана для типов time
, timestamp
и interval
в диапазоне от 0 до 6. Если в спецификации константы не указана точность, по умолчанию используется точность буквального значения (но не более 6 цифр).
8.5.1.1. Dates
В таблице 8.10 показаны некоторые возможные входные данные для типа date
.
Таблица 8.10. Ввод даты
Example | Description |
---|---|
1999-01-08 | ISO 8601;8 января в любом режиме (рекомендуемый формат) |
8 января 1999 года | однозначный в любом datestyle ввода даты |
1/8/1999 | 8 января в режиме MDY ; 1 августа в режиме DMY |
1/18/1999 | 18 января в режиме MDY ; отклонено в других режимах |
01/02/03 | 2 января 2003 г. в режиме MDY ; 1 февраля 2003 г. в режиме DMY ; 3 февраля 2001 г. в режиме YMD . |
1999-Jan-08 | 8 января в любом режиме |
Jan-08-1999 | 8 января в любом режиме |
08-Jan-1999 | 8 января в любом режиме |
99-Jan-08 | 8 января в режиме YMD , иначе ошибка |
08-Jan-99 | 8 января, кроме ошибки в режиме YMD |
Jan-08-99 | 8 января, кроме ошибки в режиме YMD |
19990108 | ISO 8601;8 января 1999 года в любом режиме. |
990108 | ISO 8601;8 января 1999 года в любом режиме. |
1999.008 | год и день года |
J2451187 | Julian date |
8 января 99 г.до н.э. | 99 год до н.э. |
8.5.1.2. Times
Типы time [ (p) ] without time zone
суток: время [(p)] без часового пояса и time [ (p) ] with time zone
. только time
эквивалентно time without time zone
.
Допустимый ввод для этих типов состоит из времени дня, за которым следует необязательный часовой пояс. (См. Таблицу 8.11 и Таблицу 8.12 .) Если часовой пояс указан во входных данных для time without time zone
, он автоматически игнорируется. Вы также можете указать дату, но она будет проигнорирована, за исключением случаев, когда вы используете название часового пояса, которое включает правило перехода на летнее время, например America/New_York
. В этом случае необходимо указать дату, чтобы определить, применяется ли стандартное или летнее время. Соответствующее смещение часового пояса записывается во time with time zone
значением часового пояса .
Таблица 8.11. Ввод времени
Example | Description |
---|---|
04:05:06.789 |
ISO 8601 |
04:05:06 |
ISO 8601 |
04:05 |
ISO 8601 |
040506 |
ISO 8601 |
04:05 AM |
как и 04:05;AM не влияет на стоимость |
04:05 PM |
то же, что 16:05; час ввода должен быть <= 12 |
04:05:06.789-8 |
ISO 8601,с часовым поясом в виде смещения UTC |
04:05:06-08:00 |
ISO 8601,с часовым поясом в виде смещения UTC |
04:05-08:00 |
ISO 8601,с часовым поясом в виде смещения UTC |
040506-08 |
ISO 8601,с часовым поясом в виде смещения UTC |
040506+0730 |
ISO 8601,с дробным часовым поясом в качестве смещения UTC |
040506+07:30:00 |
Смещение UTC,заданное в секундах (не допускается в ISO 8601) |
04:05:06 PST |
часовой пояс по аббревиатуре |
2003-04-12 04:05:06 America/New_York |
часовой пояс,указанный по полному имени |
Таблица 8.12. Ввод часового пояса
Example | Description |
---|---|
PST |
Сокращение (для тихоокеанского стандартного времени) |
America/New_York |
Имя полного часового пояса |
PST8PDT |
спецификация часового пояса в стиле POSIX |
-8:00:00 |
Смещение UTC для PST |
-8:00 |
Смещение UTC для PST (расширенный формат ISO 8601) |
-800 |
Смещение UTC для PST (базовый формат ISO 8601) |
-8 |
Смещение UTC для PST (базовый формат ISO 8601) |
zulu |
Военная аббревиатура для UTC |
z |
Краткая форма zulu (также в ISO 8601) |
Обратитесь к Разделу 8.5.3 для получения дополнительной информации о том, как указать часовые пояса.
8.5.1.3.Штампы времени
Допустимый ввод для типов отметок времени состоит из конкатенации даты и времени, за которыми следует необязательный часовой пояс, за которым следует необязательный AD
или BC
. (В качестве альтернативы, AD
/ BC
может стоять перед часовым поясом, но это не является предпочтительным порядком.) Таким образом:
1999-01-08 04:05:06
and:
1999-01-08 04:05:06 -8:00
являются действительными значениями,которые следуют стандарту ISO 8601.Кроме того,общий формат:
January 8 04:05:06 1999 PST
is supported.
Стандарт SQL различает timestamp without time zone
и timestamp with time zone
литералами часового пояса по наличию символа «+» или «-» и смещения часового пояса после времени. Отсюда, согласно стандарту,
TIMESTAMP '2004-10-19 10:23:54'
является timestamp without time zone
, в то время как
TIMESTAMP '2004-10-19 10:23:54+02'
является timestamp with time zone
. PostgreSQL никогда не проверяет содержимое литеральной строки перед определением ее типа и поэтому будет рассматривать оба вышеперечисленных значения как timestamp without time zone
. Чтобы литерал обрабатывался как timestamp with time zone
, присвойте ему правильный явный тип:
TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
В литерале, который был определен как timestamp without time zone
, PostgreSQL будет молча игнорировать любое указание часового пояса. Таким образом, результирующее значение получается из полей даты / времени во входном значении и не корректируется по часовому поясу.
Для timestamp with time zone
внутренне сохраненное значение всегда находится в формате UTC (универсальное скоординированное время, традиционно известное как среднее время по Гринвичу, GMT). Входное значение с явно заданным часовым поясом преобразуется в формат UTC с использованием соответствующего смещения для этого часового пояса. Если во входной строке часовой пояс не указан, предполагается, что он находится в часовом поясе, указанном системным параметром TimeZone , и преобразуется в формат UTC с использованием смещения timezone
пояса.
Когда выводится timestamp with time zone
значением часового пояса , она всегда конвертируется из UTC в текущий timezone
пояс и отображается как местное время в этой зоне. Чтобы увидеть время в другом часовом поясе, либо измените timezone
либо используйте конструкцию AT TIME ZONE
(см. Раздел 9.9.4 ).
Преобразования между timestamp without time zone
и timestamp with time zone
обычно предполагают, что timestamp without time zone
значения часового пояса должна быть принята или дана как местное время timezone
. Для преобразования можно указать другой часовой пояс, используя AT TIME ZONE
.
8.5.1.4.Специальные значения
PostgreSQL для удобства поддерживает несколько специальных вводных значений даты и времени, как показано в Таблице 8.13 . Значения infinity
и -infinity
специально представлены внутри системы и будут отображаться без изменений; но остальные представляют собой просто условные обозначения, которые при чтении будут преобразованы в обычные значения даты / времени. (В частности, now
и связанные строки преобразуются в определенное значение времени, как только они прочитаны.) Все эти значения должны быть заключены в одинарные кавычки при использовании в качестве констант в командах SQL.
Таблица 8.13. Специальные вводы даты / времени
Input String | Valid Types | Description |
---|---|---|
epoch |
date , timestamp
|
1970-01-01 00:00:00+00 (нулевое системное время Unix) |
infinity |
date , timestamp
|
позднее всех остальных штампов времени |
-infinity |
date , timestamp
|
раньше всех остальных штампов времени |
now |
date , time , timestamp
|
время начала текущей операции |
today |
date , timestamp
|
полночь ( 00:00 ) сегодня |
tomorrow |
date , timestamp
|
полночь ( 00:00 ) завтра |
yesterday |
date , timestamp
|
полночь ( 00:00 ) вчера |
allballs |
time |
00:00:00.00 UTC |
Следующие SQL-совместимые функции также могут использоваться для получения текущего значения времени для соответствующего типа данных: CURRENT_DATE
, CURRENT_TIME
, CURRENT_TIMESTAMP
, LOCALTIME
, LOCALTIMESTAMP
. (См. Раздел 9.9.5 .) Обратите внимание, что это функции SQL и не распознаются в строках ввода данных.
8.5. Типы дата/времени
PostgreSQL поддерживает полный список
SQL типов даты и времени, который представлен
в Table 8-9. Операции, которые
доступны для этих типов данных, описываются в
Section 9.9.
Table 8-9. Типы даты/времени
Имя | Размер хранения | Описание | Наименьшее значение | Наибольшее значение | Разрешение(шаг) |
---|---|---|---|---|---|
timestamp [ (p) ] [ without time zone ] | 8 байт | и дата и время (без часового пояса) | 4713 BC | 294276 AD | 1 микросекунда / 14 разрядов |
timestamp [ (p) ] with time zone | 8 байт | и дата и время с часовым поясом | 4713 BC | 294276 AD | 1 микросекунда / 14 разрядов |
date | 4 байта | дата (без часового пояса) | 4713 BC | 5874897 AD | 1 день |
time [ (p) ] [ without time zone ] | 8 байт | только время (без даты) | 00:00:00 | 24:00:00 | 1 микросекунда / 14 разрядов |
time [ (p) ] with time zone | 12 байт | время с часовым поясом | 00:00:00+1459 | 24:00:00-1459 | 1 микросекунда / 14 разрядов |
interval [ fields ] [ (p) ] | 12 байт | интервал времени | -178000000 лет | 178000000 лет | 1 микросекунда / 14 разрядов |
Note: Стандарт SQL требует, чтобы написание просто timestamp
было эквивалентно timestamp without time zone и
PostgreSQL следует стандарту.
(Версии до 7.3 считали, что timestamp with time zone.)
timestamptz допускается как сокращение для
timestamp with time zone; это расширенное поведение
PostgreSQL.
Для типов time, timestamp и
interval можно указывать необязательное значение
точности p, которое задаёт количество
дробных разрядов после запятой. По умолчанию, точность в явном
виде не указывается. Допустимый диапазон значения
p от 0 до 6 для типа
timestamp и для типа interval.
Note: Если значения timestamp хранятся как восьмибайтное целое число
(по умолчанию это так), то в пределах всего диапазона значений достигается
точность в одну микросекунду. Если значения timestamp хранятся
как число с плавающей запятой двойной точности (в случае выбора устаревшей
опции при компиляции), то эффективное ограничение на точность может быть
меньше 6. Значения timestamp хранятся как количество секунд
перед или после 2000-01-01. Когда значения timestamp
реализуются с использованием чисел с плавающей точкой, микросекундная
точность достигается для дат, которые находятся внутри отрезка, начинающегося
с 2000-01-01, но эта точность снижается для дат вне этого отрезка.
Обратите внимание, что значения timestamp, использующие
плавающую точку позволяют работать с большим диапазоном значений,
чем указано выше: от 4713 BC до 5874897 AD.Та же опция при
компиляции также определяет будут ли значения time
interval храниться как числа с плавающей точкой или
как восьмибайтные целые числа. В случае чисел с плавающей точкой,
бОльшие значения interval имеют меньшую точность
из-за увеличения размера самого интервала.
Для типов time разрешается диапазон p
от 0 до 6, когда для хранения значений используется восьмибайтное целое,
или от 0 до 10, когда используется число с плавающей запятой.
Тип interval имеет дополнительную опцию, которая должна
ограничивать набор сохраняемых полей с помощью одной из следующих
фраз:
YEAR MONTH DAY HOUR MINUTE SECOND YEAR TO MONTH DAY TO HOUR DAY TO MINUTE DAY TO SECOND HOUR TO MINUTE HOUR TO SECOND MINUTE TO SECOND
Обратите внимание, что если указаны и
поля и p,
то поля должны включать SECOND,
так как точность применима только к секундам.
Тип time with time zone определяется стандартом SQL,
но это определение говорит о свойствах, которые приводят нас к
сомнениям относительно пригодности. В большинстве случаев, комбинация
типов date, time, timestamp without time
zone и timestamp with time zone
предоставляет полный диапазон функциональности для работы с датой
и временем, который бы мог потребоваться какому-либо приложению.
Типы abstime и reltime имеют низкую точность
и используются для внутренних целей. Вы должны воздержаться от
использования этих типов в приложениях; эти внутренние типы в
будущем могут исчезнуть.
8.5.1. Ввод даты/времени
Вводимые значения даты и времени примаются в любом разумном формате,
включая ISO 8601, SQL-совместимый, традиционный
формат POSTGRES и другие.
Для некоторых форматов, порядок следования полей дня, месяца и года
в значении даты является неоднозначным и для таких форматов
поддерживается указание порядка следования этих полей.
Установите параметр DateStyle в значение
MDY, чтобы выбрать порядок следования месяц-день-год,
в значение DMY, чтобы выбрать порядок следования
день-месяц-год или в YMD, чтобы выбрать порядок следования
год-месяц-день.
В PostgreSQL управление вводом значений
даты и времени устроено более удобно, чем этого требует стандарт
SQL.
Смотрите Appendix B, чтобы узнать о точных
правилах разбора вводимых значений даты/времени и о распознавании
текстовых полей, включая месяцы, дни недели и часовые пояса.
Помните, что любые вводимые значения даты или времени необходимо
заключать в одиночные кавычки, как и текстовые строки. Больше
информации можно найти в Section 4.1.2.7.
SQL требует следующего синтаксиса
тип [ (p) ] 'значение'
где p является необязательной
спецификацией точности, которая задаётся целым числом,
соответствующим количеству дробрых разрядов во втором поле.
Точность может быть задана
для типов time, timestamp и
interval. Допустимые значения были рассмотрены выше.
Если точность в спецификации константы не указана, то она
устанавливается в значение по умолчанию для литеральных значений.
8.5.1.1. Даты
Table 8-10 показывает некоторые
возможные формы ввода для типа date.
Table 8-10. Ввод значений даты
Пример | Описание |
---|---|
1999-01-08 | ISO 8601; 8 января в любом режиме (рекомендуемый формат) |
January 8, 1999 | значение является однозначным для любого datestyle режима ввода |
1/8/1999 | 8 января в режиме MDY; 1 августа в режиме DMY |
1/18/1999 | 18 января в режиме MDY; в других режимах значение будет отвергнуто |
01/02/03 | 2 января, 2003 года в режиме MDY; 1 февраля, 2003 года в режиме DMY; 3 февраля, 2001 года в режиме YMD |
1999-Jan-08 | 8 января в любом режиме |
Jan-08-1999 | 8 января в любом режиме |
08-Jan-1999 | 8 января в любом режиме |
99-Jan-08 | 8 января в режиме YMD, иначе ошибка |
08-Jan-99 | 8 января, за исключением режима YMD, в котором будет ошибка |
Jan-08-99 | 8 января, за исключением режима YMD, в котором будет ошибка |
19990108 | ISO 8601; 8 января, 1999 года в любом режиме |
990108 | ISO 8601; 8 января, 1999 года в любом режиме |
1999.008 | год и номер дня в году |
J2451187 | день по Юлианскому календарю |
January 8, 99 BC | 99 год до Нашей Эры |
8.5.1.2. Время
Типами времени являются time [
(p) ] without time zone и
time [ (p) ] with time
zone. Тип time эквивалентен типу
time without time zone.
Правильные вводимые значения для этих типов состоят из
времени дня, за которым следует необязательное значение
часового пояса. (См. Table 8-11
и Table 8-12.) Если часовой пояс
задаётся при вводе значений типа time without time zone,
то она молча игнорируется. Вы также всегда можете задать дату, но
она будет проигнорирована, за исключением случая, когда вы используете
имя часового пояса, который реализует переход на летнее/зимнее время,
как например America/New_York.
В этом случае требуется указание даты, чтобы понять летнее или зимнее время
нужно применять. Соответствующее смещение часового пояса записывается в
значение time with time zone.
Table 8-11. Ввод значений времени
Пример | Описание |
---|---|
04:05:06.789 | ISO 8601 |
04:05:06 | ISO 8601 |
04:05 | ISO 8601 |
040506 | ISO 8601 |
04:05 AM | тоже, что и 04:05; AM не имеет значения |
04:05 PM | тоже, что и 16:05; значение часа должно быть <= 12 |
04:05:06.789-8 | ISO 8601 |
04:05:06-08:00 | ISO 8601 |
04:05-08:00 | ISO 8601 |
040506-08 | ISO 8601 |
04:05:06 PST | часовой пояс, заданный аббревиатурой |
2003-04-12 04:05:06 America/New_York | часовой пояс, заданный полным именем |
Table 8-12. Ввод значений часового пояса
Пример | Описание |
---|---|
PST | Аббревиатура (от Pacific Standard Time) |
America/New_York | Полное имя часового пояса |
PST8PDT | спецификация часового пояса в стиле POSIX |
-8:00 | ISO-8601 смещение для PST |
-800 | ISO-8601 смещение для PST |
-8 | ISO-8601 смещение для PST |
zulu | Военная аббревиатура для UTC |
z | Краткая форма zulu |
Информацию о том как задавать часовые пояса, см. в Appendix B.
8.5.1.3. Дата и время
Правильные вводимые значения для типа даты и времени состоят из
слитного значения даты и времени, за которым следует необязательное
значение часового пояса, за которым следует необязательный суффикс
AD или BC.
(В качестве альтернативы, AD/BC могут
указываться перед часовым поясом, но так делать не рекомендуется.)
Таким образом:
1999-01-08 04:05:06
и:
1999-01-08 04:05:06 -8:00
являются правильными значениями, которые соответствуют стандарту
ISO 8601. В дополнение, поддерживается
формат стандарта:
January 8 04:05:06 1999 PST
Стандарт SQL различает литералы типов timestamp
without time zone и timestamp with time zone по
наличию символа «+»; или «-« и смещения
часового пояса, которое задаётся после времени. Следовательно, в
соответствии со стандартом,
TIMESTAMP '2004-10-19 10:23:54'
имеет тип timestamp without time zone, в то время как
значение
TIMESTAMP '2004-10-19 10:23:54+02'
имеет тип timestamp with time zone.
PostgreSQL никогда не проверяет содержимое
строки литерала перед тем как определить его тип и таким образом будет
считать оба данных выше примера как timestamp without time zone.
Чтобы убедиться, что литерал распознается как timestamp with
time zone, придайте ему правильный для этого типа вид:
TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
В литерале, который распознан как timestamp without
timezone, PostgreSQL будет молча
игнорировать любые указания часового пояса.
Таким образом, итоговое значение будет получено из полей
даты/времени, которые присутствуют во вводимом значении и не будет
скорректировано по часовому поясу.
Для типа timestamp with time zone, значение внутри, всегда
хранится в UTC (Universal Coordinated Time, обычно известное как
время Гринвичского (нулевого) меридиана или GMT).
Вводимое значение, которое имеет явно заданный часовой пояс,
конвертируется в UTC, используя смещение, соответствующее этому
часовому поясу. Если при вводе часовой пояс не указан, то
он берётся из системного параметра timezone
и точно также конвертируется в UTC, используя смещение часового
пояса, указанное в переменной timezone.
Когда выводится значение типа timestamp with time zone,
оно всегда конвертируется из UTC в часовой пояс, заданный
переменной timezone и отображается как местное время в
этом поясе. Чтобы увидеть это же время в другом часовом поясе, или
измените значение переменной timezone или используйте
конструкцию AT TIME ZONE
(см. Section 9.9.3).
Преобразования между типами timestamp without time zone
и timestamp with time zone обычно означают, что
значение timestamp without time zone должно быть
принято или выдано как значение местного времени часового
пояса, указанного в переменной timezone. Для
преобразования с помощью AT TIME ZONE может
быть задан другой часовой пояс.
8.5.1.4. Специальные значения
PostgreSQL поддерживает для удобства
несколько специальных значений ввода даты/времени, как показано в
Table 8-13. Из этих значений
infinity и -infinity
специально представлены внутри СУБД и будут показываться в
неизменном виде; но другие являются просто сокращениями, которые будут
преобразованы при вводе в обычные значения даты/времени.
(Например, now и подобные строки преобразоываются
в определённое значение времени в момент, когда их читает СУБД.)
Все такие значения, когда они используется в командах SQL,
должны заключаться в одинарные кавычки.
Table 8-13. Специальные значения даты/времени
Вводимая строка | Допустимые типы данных | Описание |
---|---|---|
epoch | date, timestamp | 1970-01-01 00:00:00+00 (Нулевое системное время Unix) |
infinity | date, timestamp | позже, чем все другие временные штампы |
-infinity | date, timestamp | раньше, чем все другие временные штампы |
now | date, time, timestamp | время начала текущей транзакции |
today | date, timestamp | полночь текущего дня |
tomorrow | date, timestamp | полночь завтрашнего дня |
yesterday | date, timestamp | полночь вчерашнего дня |
allballs | time | 00:00:00.00 UTC |
Также получить текущее значение времени для соответствующих типов
данных можно используя следующие, совместимые со стандартом
SQL функции:
CURRENT_DATE, CURRENT_TIME,
CURRENT_TIMESTAMP, LOCALTIME,
LOCALTIMESTAMP. Последние четыре функции позволяют
задать необязательную точность. (См. Section 9.9.4.) Обратите внимание, что есть
функции SQL и что они не рапознаются при вводе.
8.5.2. Вывод даты/времени
Формат вывода значений типов даты/времени может быть установлен в
одном из четырёх стилей: ISO 8601, SQL (Ingres),
традиционный POSTGRES (Формат Unix date)
или German. По умолчанию устанавливается
формат ISO. (Стандарт
SQL требует использования формата ISO 8601.
Название формата вывода «SQL» является исторически
сложившимся.) Table 8-14
показывает примеры каждого стиля вывода. Вывод значений типов
date и time, разумеется, только часть
даты и времени в соответствии с данными примерами.
Table 8-14. Стили вывода даты/времени
Спецификация стиля | Описание | Пример |
---|---|---|
ISO | стандарт ISO 8601/SQL | 1997-12-17 07:37:16-08 |
SQL | традиционный стиль | 12/17/1997 07:37:16.00 PST |
POSTGRES | собственный стиль | Wed Dec 17 07:37:16 1997 PST |
German | региональный стиль | 17.12.1997 07:37:16.00 PST |
В стилях SQL и POSTGRES, день идёт перед месяцем, если
быд задан порядок полей DMY, в противном случае месяц следует перед днём.
(См. Section 8.5.1
о том как эта настройка также отказывает эффект на интерпретацию вводимых
значений.) В Table 8-15 показан
пример.
Table 8-15. Соглашения по датам
Установка стиля даты | Входимое значение | Пример вывода |
---|---|---|
SQL, DMY | день/месяц/год | 17/12/1997 15:37:16.00 CET |
SQL, MDY | месяц/день/год | 12/17/1997 07:37:16.00 PST |
Postgres, DMY | день/месяц/год | Wed 17 Dec 07:37:16 1997 PST |
Пользователь может выбрать нужные ему стили даты/времени с
помощью команды SET datestyle, параметра DateStyle в конфигурационном файле
postgresql.conf или с установив переменную
окружения PGDATESTYLE на сервере или клиенте.
Функция форматирования to_char
(см. Section 9.8) также может быть
использована как более гибкий способ форматирования выводимых
значений даты/времени.
8.5.3. Часовые пояса
Часовые пояса и соглашения по часовым поясам зависят не только
геометрии планеты, но и от политического территориального деления.
Часовые пояса во всём мире стали чем-то стандартным, начиная с
1900 года, но продолжают соответствующим образом изменяться, учитывая
правила летнего/зимнего времени. В настоящий момент
PostgreSQL использует широко-использумую
базу данных zoneinfo часовых поясом для получения
информации о исторических правилах часовых поясов. Для времени в будущем,
берутся последние известные правила для данного часового пояса,
которые продолжат изменяться в неопределённо далёком будущем.
PostgreSQL прикладывает усилия для
совместимости со стандартом SQL при работе с
типовыми операциями. Однако, в стандарте SQL
есть разнородная смесь типов даты и времени, а также совместимых
с ними типов. Вот две очевидные проблемы:
-
Хотя тип date не имеет ассоциированный с ним
часовой пояс, тип time может её иметь.
Часовые пояса в реальном мире имеют небольшую значимость,
в отличие от ассоциированных с ними дат и времени, а
смещение может изменяться в течении года при переходе на
зимнее/летнее время. -
Часовой пояс по умолчанию задаётся как постоянное цифровое
смещение от времени UTC. Таким образом, становится
невозможным адаптировать арифметические операции над датой/временем
при переходе на летнее/зимнее время при работе с DST.
Из-за этих трудностей, мы рекомендуем при использовании часовых
поясов использовать типы даты/времени, которые содержат как дату, так
и время. Мы не рекомендуем использовать
тип time with time zone (хотя он поддерживается
PostgreSQL для старых приложений и
для совместимости со стандартом SQL).
PostgreSQL для всех типов, которые
содержат только дату или только время использует ваш локальный
часовой пояс.
Все даты, учитывающие часовой пояс и время хранятся внутри в
UTC. Перед тем как значения будут выданы клиенту,
они преобразуются в локальное время в соответствии с настройками,
указанными в параметре timezone.
PostgreSQL позволяет указывать часовые пояса в
трёх различных формах:
-
Полное имя часового пояса, например America/New_York.
Распознаваемые имена часовых поясов перечислены в
pg_timezone_names (см. Section 45.67).
PostgreSQL использует для этой цели
широко-используемые данные о часовых поясах zoneinfo,
так что одни и те же имена также распоздаются и многими другими
программами. -
Аббревиатура имени часового пояса, например PST.
Такие аббревиатуры часто задают конкретное смещение от UTC, в
отличии от полных имён часовых поясов, которые могут неявно
устанавливать также и правила перехода на летнее/зимнее время.
Распознаваемые аббревиатуры перечислены в
pg_timezone_abbrevs (см. (see Section 45.66). Вы не можете установить
конфигурационные параметры timezone или
log_timezone для аббревиатуры
часового пояса, но вы можете использовать аббревиатуры при
вводе значений даты/времени и с оператором AT TIME ZONE. -
В дополнение к именам часовых поясов и их аббревиатурам,
PostgreSQL будет работать и со спецификациями
часовых поясов в стиле POSIX в форме STDсмещение
или STDсмещениеDST, где
STD — аббревиатура часового пояса, смещение
— это числовое смещение в часах на запад от UTC, и DST
— это необязательная аббревиатура летнего/зимнего времени, установленная
на одночасовое опережение заданного смещения. Например, если значение
EST5EDT не является уже распознаным именем часового пояса,
оно будет принято и будет функционально эквивалентно времени
Восточного Побережья США. Когда вводится имя часового пояса с переходом
на летнее/зимнее время, работа с ним будет осуществляться в соответствии
с такими же правилами перехода на летнее/зимнее время, которые используются
в базе данных zoneinfo в записи posixrules.
В стандартной установке PostgreSQL,
posixrules — это тоже самое, что и US/Eastern,
так что спецификации часовых поясов в стиле POSIX, следуют правилам
перехода на летнее/зимнее время, применяемым в США. Если необходимо,
вы можете настроить это, заменив файл posixrules.
Вкратце, отличие между аббревиатурами и полными
именами: аббревиатуры всегда предоставляют постоянное смещение от UTC,
в то время как полные имена подразумевают локальное правило перехода
на летнее/зимнее время и таким образом имееют два возможных смещения от UTC.
Использовать часовые пояса в стиле POSIX, нужно осторожно,
потому что данная возможность позволяет задать некорректные значения,
которые будут приняты без сообщений об ошибках, поскольку проверка
аббревиатур не производится. Например, SET TIMEZONE TO FOOBAR0
будет работать, но фактически система будет продолжать использовать
данное значение как аббревиатуру для UTC. Кроме того, нужно помнить,
что в именах часовых поясов POSIX, положительные смещения используются
для поясов к западу от Гринвича. Во всём остальном
PostgreSQL следует соглашению ISO-8601,
что положительные смещения часовых поясов используются для поясов к
востоку от Гринвича.
Во всех случаях, имена часовых поясов распознаются независимо от регистра
букв. (Ранее в некоторых контекстах была зависимость от регистра, в других
нет, но это изменилось, начиная с PostgreSQL
версии 8.2.)
Ни полные имена ни аббревиатуры не являются жёстко встроенными в сервер;
они берутся из конфигурационных файлов, хранящихся в подкаталогах
…/share/timezone/ и …/share/timezonesets/
относительно каталога установки.
(см. Section B.3).
Параметр timezone может быть установлен в
файле postgresql.conf или любым другим стандартным
способом, описанным в Chapter 18.
Также существует несколько специальных способов его установки:
-
Если параметр timezone не задан в файле
postgresql.conf или как опция командной строки для
сервера, то сервер пытается использовать в качестве часового
пояса по умолчанию значение переменной окружения TZ.
Если переменная TZ не задана или не содержит в
себе одно из известных PostgreSQL
значений имени часового пояса, то сервер пытается определить
часовой пояс по умолчанию у операционной системы, проверяя
поведение библиотечной функции C localtime().
Часовой пояс по умолчанию выбирается в этом случае, как
наиболее подходящий из известных PostgreSQL
часовых поясов. (Эти правила также используются для выбора
значения по умолчанию log_timezone,
если оно не указано.) -
В SQL команда SET TIME ZONE
устанавливает часовой пояс для текущей сессии. Эта команда
является альтернативой команды SET TIMEZONE TO,
которая имеет более соответствующий SQL синтаксис. -
Если на стороне клиента установлена переменная окружения
PGTZ, то при подключении к серверу, её значение
будет использоваться для отправки на сервер команды
SET TIME ZONE, если клиентское приложение
использует библиотеку libpq.
8.5.4. Ввод значений интервала
Значения типа interval могу быть записаны, используя
следующий подробный синтаксис:
[@] длительность элемент [длительность элемент...] [направление]
где длительность является числом (возможно со знаком);
элемент может принимать значение microsecond(микросекунда),
millisecond(миллисекунда), second(секунда),
minute(минута), hour(час), day(день),
week(неделя), month(месяц), year(год),
decade(декада), century(век),
millennium(тысячелетие) или аббревиатуры одного из этих значений;
направление может принимать значения ago(назад) или
не указываться. Знак (@) также является необязательным. В соответствии
с указанным знаком, будет добавлено необходимое количество различных элементов.
ago изменяет знак у всех полей. Данный синтаксис также используется
при выводе значений типа interval. Если переменная
IntervalStyle установлена в postgres_verbose.
Длительность в днях, часах, минутах и секундах может быть указана без
явных указаний соответствующих элементов. Например, ‘1 12:59:10’
читается также как и ‘1 day 12 hours 59 min 10 sec’.
Комбинация лет и месяцев может быть также задана с использованием знака
чёрточки; например, ‘200-10’ читается также как и
‘200 years 10 months’. (Эти укороченные формы фактически
являются единственными из тех, что разрешает стандарт SQL
и используются для вывода, когда значение IntervalStyle
установлено в sql_standard.)
Значения типа interval могут также записываться по
стандарту ISO 8601, используя либо «формат с указателями»,
согласно разделу стандарта 4.4.3.2, либо «альтернативный формат»
раздела 4.4.3.3. Формат с указателями выглядит так:
P длительность элемент [ длительность элемент ...] [ T [ длительность элемент ...]]
Строка должна начинаться с P и может включать
T, которые указывают на элементы времени дня.
Доступные аббревиатуры элементов можно найти в
Table 8-16. Элементы могут
быть опущены и могут быть указаны в любом порядке, но элементы
меньшие, чем день должны указываться после T.
В особенности это касается M, смысл которого зависит
от того, будет ли он указан до или после T.
Table 8-16. Аббревиатуры элементов интервалов по стандарту ISO 8601
Аббревиатура | Пояснение |
---|---|
Y | Годы |
M | Месяцы (в части, касающейся даты) |
W | Недели |
D | Дни |
H | Часы |
M | Минуты (в части, касающейся времени) |
S | Секунды |
В альтернативном формате:
P [ годы-месяцы-дни ] [ T часы:минуты:секунды ]
строка должна начинаться с P, а
T отделяет в интервале дату от времени.
Значения задаются цифрами сходным с ISO 8601 образом.
При записи констант-интервалов по спецификации полей,
или при назначении строки для колонки интервала, которая была задана по
спецификации полей, интерпретация длительностей без явного
указания элемента зависит от самих полей.
Например, INTERVAL ‘1’ YEAR читается как 1 год, в то время
как INTERVAL ‘1’ означает 1 секунду. Кроме того, значения полей
«справа» от наименее значимого поля, разрешённого
спецификацией полей, молча отбрасываются. Например,
в результате написания INTERVAL ‘1 day 2:03:04’ HOUR TO MINUTE
будет отброшено поле секунд, но не поле дня.
В соответствии со стандартом SQL, все поля в значении
интервала должны иметь один и тот же знак, так что начальный знак
минус применяется ко всем полям; например знак минус в значении
‘-1 2:03:04’ применяется и к дням и к часам/минутам/секундам.
PostgreSQL позволяет полям иметь разные знаки и
традиционно считает каждое поле в текстовом представлении как
независящее от знака, так что часть час/минута/секунда рассматривается
в данном выше примере как положительная. Если значение переменной
IntervalStyle установлено в sql_standard,
то начальный знак применяется ко всем полям (а не только к тем, где
нет дополнительного знака). В противном случае, используется
традиционная для PostgreSQL интерпретация. Чтобы
избежать путаницы, рекомендуется явно указывать знак для каждого
поля, если какое-либо поле является отрицательным.
Внутри значения interval хранятся как месяцы, дни и
секунды. Так сделано, потому что количество дней в месяце
разное, а продолжительность дня может быть 23 или 25 часов,
если используется переход на летнее/зимнее время. Поля месяцы и
дни являются целыми, а поле секунд может хранится в виде дроби.
Поскольку интервалы обычно создаются из строковых констант или
как разность значений timestamp, данный метод хранения
хорошо работает в большинстве случаев. Для коррекции дней и
часов, которые перекрывают их нормальные диапазоны, доступны
функции justify_days
и justify_hours
.
В подробном формате ввода и в некоторых полях более компактных
форматов ввода, значения могут быть указаны в виде дробей;
например ‘1.5 week’ или ’01:02:03.45′.
Такой ввод для хранения преобразуется в соответствующее количество
месяцев, дней и секунд. Преобразование выполняется по следующим
правилам: 1 месяц = 30 дней, 1 день = 24 часам. Например,
‘1.5 month’ будет 1 месяц и 15 дней. Только секунды
будут показываться при выводе в виде дроби.
Table 8-17 показывает
некоторые примеры допустимого ввода значений interval.
Table 8-17. Вводимые значения интервала
Пример | Описание |
---|---|
1-2 | формат по стандарту SQL: 1 год 2 месяца |
3 4:05:06 | формат по стандарту SQL: 3 дня 4 часа 5 минут 6 секунд |
1 year 2 months 3 days 4 hours 5 minutes 6 seconds | Традиционный формат Postgres: 1 год 2 месяца 3 дня 4 часа 5 минут 6 секунд |
P1Y2M3DT4H5M6S | ISO 8601 «формат с указателями»: то же значение, что и выше |
P0001-02-03T04:05:06 | ISO 8601 «альтернативный формат»: то же значение, что и выше |
8.5.5. Вывод значений интервала
Формат вывода значений типа interval может быть одним
из четырёх: sql_standard, postgres,
postgres_verbose или iso_8601,
в зависимости от использования команды SET intervalstyle.
По умолчанию установлен формат postgres.
Table 8-18 показывает примеры
каждого стиля вывода.
Стиль sql_standard выводит строковые значения, соответствующие
стандарту SQL, если значения интервала соответствуют ограничениям
стандарта (либо только год-месяц, либо только день-время, без смешивания
положительных и отрицательных компонентов). В противном случае, вывод
выглядит как стандартный литерал год-месяц, зак оторым следует литерал
день-время с явным указанием знаков, добавляемых для избегания путницы
на смешанных по знакам интервалах.
Вывод стиля postgres совпадает с выводом, который был в
PostgreSQL до версии 8.4, когда параметр
DateStyle был установлен в ISO.
Вывод стиля postgres_verbose совпадает с выводом
PostgreSQL до версии 8.4, когда параметр
DateStyle был установлен в не-ISO.
Вывод стиля iso_8601 совпадает с «форматом
с указателями», который описан в разделе 4.4.3.2 стандарта
ISO 8601.
Table 8-18. Примеры вывода значений интервала в разных стилях
Спецификация стиля | Интервал Год-Месяц | Интервал День-Время | Смешанный интервал |
---|---|---|---|
sql_standard | 1-2 | 3 4:05:06 | -1-2 +3 -4:05:06 |
postgres | 1 year 2 mons | 3 days 04:05:06 | -1 year -2 mons +3 days -04:05:06 |
postgres_verbose | @ 1 year 2 mons | @ 3 days 4 hours 5 mins 6 secs | @ 1 year 2 mons -3 days 4 hours 5 mins 6 secs ago |
iso_8601 | P1Y2M | P3DT4H5M6S | P-1Y-2M3DT-4H-5M-6S |
8.5.6. Некоторые подробности по датам
PostgreSQL использует для всех вычислений
даты/времени Юлианский календарь. У него есть прекрасное свойство
вычислять любую дату, которая следует за годом 4713 до н.э.,
потому что используется длина года, равная 365.2425 дням.
Соглашения по датам, которые использовались до 19-го века интересны,
но не позволяют целостно использовать их при кодировании обработки
даты/времени.
PostgreSQL поддерживает полный набор типов даты и времени SQL.Даты отсчитываются по григорианскому календарю, даже за годы до введения этого календаря.
Name | Storage Size | Description | Low Value | High Value | Resolution |
---|---|---|---|---|---|
timestamp [ (p) ] [ without time zone ] |
8 bytes | both date and time (no time zone) | 4713 BC | 294276 AD | 1 microsecond |
timestamp [ (p) ] with time zone |
8 bytes | both date and time, with time zone | 4713 BC | 294276 AD | 1 microsecond |
date |
4 bytes | date (no time of day) | 4713 BC | 5874897 AD | 1 day |
time [ (p) ] [ without time zone ] |
8 bytes | time of day (no date) | 00:00:00 | 24:00:00 | 1 microsecond |
time [ (p) ] with time zone |
12 bytes | time of day (no date), with time zone | 00:00:00+1559 | 24:00:00-1559 | 1 microsecond |
interval [ fields ] [ (p) ] |
16 bytes | time interval | -178000000 years | 178000000 years | 1 microsecond |
time
, timestamp
, и interval
принять необязательное значение точности p
, указывающее количество дробных цифр, сохраняемых в поле секунд. По умолчанию нет явного ограничения точности. Допустимый диапазон p
от 0 до 6.
У interval
типа есть дополнительная опция, которая заключается в том, чтобы ограничить набор хранимых полей, написав одну из следующих фраз:
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
YEAR TO MONTH
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND
Обратите внимание, что если указаны оба значения fields
и , значение должно включать , поскольку точность применяется только к секундам.pfieldsSECOND
Тип time with time zone
определен стандартом SQL, но определение демонстрирует свойства, которые приводят к сомнительной полезности. В большинстве случаев комбинация date
, time
, timestamp without time zone
и timestamp with time zone
должна обеспечивать полный набор функций даты/времени, необходимых любому приложению.
Ввод даты/времени
Ввод даты и времени принимается практически в любом приемлемом формате, включая ISO 8601, SQL — совместимый, традиционный POSTGRES и другие. Для некоторых форматов порядок дня, месяца и года при вводе даты неоднозначен, и поддерживается указание ожидаемого порядка этих полей. Установите для параметра DateStyleMDY
значение , чтобы выбрать интерпретацию месяц-день-год, DMY
выбрать интерпретацию день-месяц-год или YMD
выбрать интерпретацию год-месяц-день.
PostgreSQL более гибок в обработке ввода даты/времени, чем требует стандарт SQL. Для точных правил синтаксического анализа ввода даты/времени и для распознаваемых текстовых полей, включая месяцы, дни недели и часовые пояса.
Помните, что любой литерал даты или времени должен быть заключен в одинарные кавычки, как текстовые строки. Для получения дополнительной информации. SQL требует следующего синтаксиса
type [ (p) ] 'value'
где p
— необязательная спецификация точности, указывающая количество дробных цифр в поле секунд. Точность может быть указана для типов time
, , timestamp
и interval
и может варьироваться от 0 до 6. Если точность не указана в спецификации константы, по умолчанию используется точность буквального значения (но не более 6 цифр).
Даты
Ниже показаны некоторые возможные входные данные для данного date
типа.
Example | Description |
---|---|
1999-01-08 | ISO 8601; January 8 in any mode (recommended format) |
January 8, 1999 | unambiguous in any datestyle input mode |
1/8/1999 | January 8 in MDY mode; August 1 in DMY mode |
1/18/1999 | January 18 in MDY mode; rejected in other modes |
01/02/03 | January 2, 2003 in MDY mode; February 1, 2003 in DMY mode; February 3, 2001 in YMD mode |
1999-Jan-08 | January 8 in any mode |
Jan-08-1999 | January 8 in any mode |
08-Jan-1999 | January 8 in any mode |
99-Jan-08 | January 8 in YMD mode, else error |
08-Jan-99 | January 8, except error in YMD mode |
Jan-08-99 | January 8, except error in YMD mode |
19990108 | ISO 8601; January 8, 1999 in any mode |
990108 | ISO 8601; January 8, 1999 in any mode |
1999.008 | year and day of year |
J2451187 | Julian date |
January 8, 99 BC | year 99 BC |
Время
Типы времени суток: и . один эквивалентен .time [ (p) ] without time zonetime [ (p) ] with time zonetimetime without time zone
Допустимый ввод для этих типов состоит из времени суток, за которым следует необязательный часовой пояс. Если часовой пояс указан во входных данных для time without time zone
, он просто игнорируется. Вы также можете указать дату, но она будет проигнорирована, за исключением случаев, когда вы используете имя часового пояса, включающее правило перехода на летнее время, например America/New_York
. В этом случае указание даты требуется для того, чтобы определить, применяется ли стандартное или летнее время. Соответствующее смещение часового пояса записывается в time with time zone
значение.
Example | Description |
---|---|
04:05:06.789 |
ISO 8601 |
04:05:06 |
ISO 8601 |
04:05 |
ISO 8601 |
040506 |
ISO 8601 |
04:05 AM |
same as 04:05; AM does not affect value |
04:05 PM |
same as 16:05; input hour must be <= 12 |
04:05:06.789-8 |
ISO 8601, with time zone as UTC offset |
04:05:06-08:00 |
ISO 8601, with time zone as UTC offset |
04:05-08:00 |
ISO 8601, with time zone as UTC offset |
040506-08 |
ISO 8601, with time zone as UTC offset |
040506+0730 |
ISO 8601, with fractional-hour time zone as UTC offset |
040506+07:30:00 |
UTC offset specified to seconds (not allowed in ISO 8601) |
04:05:06 PST |
time zone specified by abbreviation |
2003-04-12 04:05:06 America/New_York |
time zone specified by full name |
Example | Description |
---|---|
PST |
Abbreviation (for Pacific Standard Time) |
America/New_York |
Full time zone name |
PST8PDT |
POSIX-style time zone specification |
-8:00:00 |
UTC offset for PST |
-8:00 |
UTC offset for PST (ISO 8601 extended format) |
-800 |
UTC offset for PST (ISO 8601 basic format) |
-8 |
UTC offset for PST (ISO 8601 basic format) |
zulu |
Military abbreviation for UTC |
z |
Short form of zulu (also in ISO 8601) |
Отметки времени
Допустимый ввод для типов отметок времени состоит из конкатенации даты и времени, за которыми следует необязательный часовой пояс, за которым следует необязательный AD
или BC
. (В качестве альтернативы AD
/ BC
может стоять перед часовым поясом, но это не предпочтительный порядок.) Таким образом:
1999-01-08 04:05:06
и:
1999-01-08 04:05:06 -8:00
действительные значения, соответствующие стандарту ISO 8601. Кроме того, общий формат:
8 января 04:05:06 1999 PST
поддерживается.
Стандарт SQL различает литералы наличием символа « + » или « — »timestamp without time zone
и смещением часового пояса после времени. Отсюда, согласно стандарту,timestamp with time zone
TIMESTAMP '2004-10-19 10:23:54'
является timestamp without time zone
, в то время как
TIMESTAMP '2004-10-19 10:23:54+02'
является timestamp with time zone
. PostgreSQL никогда не проверяет содержимое литеральной строки перед определением ее типа, и поэтому будет рассматривать оба вышеуказанных варианта как timestamp without time zone
. Чтобы убедиться, что литерал обрабатывается как timestamp with time zone
, задайте ему правильный явный тип:
TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
В литерале, который определен как timestamp without time zone
, PostgreSQL молча игнорирует любое указание часового пояса. То есть результирующее значение получается из полей даты/времени во входном значении и не корректируется для часового пояса.
Для timestamp with time zone
внутренне сохраненное значение всегда находится в формате UTC (универсальное скоординированное время, традиционно известное как среднее время по Гринвичу, GMT ). Входное значение с явно заданным часовым поясом преобразуется в формат UTC с использованием соответствующего смещения для этого часового пояса. Если во входной строке часовой пояс не указан, предполагается, что он находится в часовом поясе, указанном системным параметром TimeZone , и преобразуется в формат UTC с использованием смещения для timezone
зоны.
Когда timestamp with time zone
значение выводится, оно всегда преобразуется из UTC в текущую timezone
зону и отображается как местное время в этой зоне. Чтобы увидеть время в другом часовом поясе, либо измените, timezone
либо используйте AT TIME ZONE
конструкцию.
Преобразования между timestamp without time zone
и timestamp with time zone
обычно предполагают, что timestamp without time zone
значение должно быть взято или задано как timezone
местное время. Для преобразования можно указать другой часовой пояс с помощью AT TIME ZONE
.
Специальные Значения
PostgreSQL для удобства поддерживает несколько специальных входных значений даты/времени. Значения infinity
и -infinity
специально представлены внутри системы и будут отображаться без изменений; но другие являются просто условными обозначениями, которые при чтении будут преобразованы в обычные значения даты/времени. (В частности, now
связанные строки преобразуются в определенное значение времени, как только они считываются.) Все эти значения должны быть заключены в одинарные кавычки при использовании в качестве констант в командах SQL.
Input String | Valid Types | Description |
---|---|---|
epoch |
date , timestamp |
1970-01-01 00:00:00+00 (Unix system time zero) |
infinity |
date , timestamp |
later than all other time stamps |
-infinity |
date , timestamp |
earlier than all other time stamps |
now |
date , time , timestamp |
current transaction’s start time |
today |
date , timestamp |
midnight (00:00 ) today |
tomorrow |
date , timestamp |
midnight (00:00 ) tomorrow |
yesterday |
date , timestamp |
midnight (00:00 ) yesterday |
allballs |
time |
00:00:00.00 UTC |
Следующие функции, совместимые с SQL , также можно использовать для получения текущего значения времени для соответствующего типа данных: CURRENT_DATE
, CURRENT_TIME
, CURRENT_TIMESTAMP
, LOCALTIME
, LOCALTIMESTAMP
. Обратите внимание, что это функции SQL, и они не распознаются в строках ввода данных.
Хотя входные строки
now
,today
,tomorrow
иyesterday
прекрасно подходят для использования в интерактивных командах SQL, они могут иметь неожиданное поведение, когда команда сохраняется для последующего выполнения, например, в подготовленных операторах, представлениях и определениях функций. Строка может быть преобразована в определенное значение времени, которое продолжает использоваться еще долгое время после того, как оно устареет. Вместо этого в таких контекстах используйте одну из функций SQL. Например,CURRENT_DATE + 1
безопаснее, чем'tomorrow'::date
.
Вывод даты/времени
Формат вывода типов даты/времени может быть установлен в один из четырех стилей ISO 8601, SQL (Ingres), традиционный POSTGRES (формат даты Unix ) или немецкий. По умолчанию используется формат ISO . (Стандарт SQL требует использования формата ISO 8601. Название выходного формата « SQL » является исторической случайностью.) Вывод типов date
и time
обычно представляет собой только часть даты или времени в соответствии с приведенными примерами. Однако стиль POSTGRES выводит только значения даты в формате ISO .
Style Specification | Description | Example |
---|---|---|
ISO |
ISO 8601, SQL standard | 1997-12-17 07:37:16-08 |
SQL |
traditional style | 12/17/1997 07:37:16.00 PST |
Postgres |
original style | Wed Dec 17 07:37:16 1997 PST |
German |
regional style | 17.12.1997 07:37:16.00 PST |
В стилях SQL и POSTGRES день отображается перед месяцем, если указан порядок полей DMY, в противном случае месяц отображается перед днем.
datestyle Setting |
Input Ordering | Example Output |
---|---|---|
SQL, DMY |
day /month /year |
17/12/1997 15:37:16.00 CET |
SQL, MDY |
month /day /year |
12/17/1997 07:37:16.00 PST |
Postgres, DMY |
day /month /year |
Wed 17 Dec 07:37:16 1997 PST |
В стиле ISO часовой пояс всегда отображается как числовое смещение со знаком от UTC с положительным знаком, используемым для зон к востоку от Гринвича. Смещение будет отображаться как hh
(только часы), если это целое число часов, иначе как hh
: mm
если это целое число минут, иначе как hh
: mm
: ss
. (Третий случай невозможен с любым современным стандартом часовых поясов, но он может появиться при работе с отметками времени, которые предшествуют принятию стандартизированных часовых поясов.) В других стилях даты часовой пояс отображается как буквенная аббревиатура, если он широко используется в текущей зоне. В противном случае он отображается как числовое смещение со знаком в базовом формате ISO 8601 ( hh
или hhmm
).
Пользователь может выбрать стиль даты/времени с помощью SET datestyle
команды, параметра DateStyle в postgresql.conf
файле конфигурации или PGDATESTYLE
переменной среды на сервере или клиенте.
Функция форматирования to_char
также доступна как более гибкий способ форматирования вывода даты/времени.
Часовые пояса
Часовые пояса и соглашения о часовых поясах зависят от политических решений, а не только от геометрии Земли. Часовые пояса по всему миру стали несколько стандартизированными в 1900-х годах, но по-прежнему подвержены произвольным изменениям, особенно в отношении правил перехода на летнее время. PostgreSQL использует широко используемую базу данных часовых поясов IANA (Olson) для получения информации об исторических правилах часовых поясов. Для времени в будущем предполагается, что самые последние известные правила для данного часового пояса будут продолжать соблюдаться бесконечно далеко в будущем.
PostgreSQL стремится быть совместимым со стандартными определениями SQL для типичного использования. Однако стандарт SQL имеет странное сочетание типов и возможностей даты и времени. Две очевидные проблемы:
- Хотя
date
тип не может иметь связанный часовой пояс,time
тип может. Часовые пояса в реальном мире не имеют большого значения, если они не связаны с датой и временем, поскольку смещение может меняться в течение года с границами летнего времени. - Часовой пояс по умолчанию указывается как постоянное числовое смещение от UTC . Поэтому невозможно адаптироваться к летнему времени при выполнении арифметических операций с датой/временем за границами летнего времени .
Чтобы устранить эти трудности, мы рекомендуем использовать типы даты/времени, которые содержат дату и время при использовании часовых поясов. Мы не рекомендуем использовать этот тип time with time zone
(хотя он поддерживается PostgreSQL для устаревших приложений и для соответствия стандарту SQL ). PostgreSQL предполагает ваш местный часовой пояс для любого типа, содержащего только дату или время.
Все даты и время с учетом часового пояса хранятся внутри в формате UTC . Они преобразуются в местное время в зоне, указанной параметром конфигурации TimeZone , прежде чем отображаться клиенту.
PostgreSQL позволяет указывать часовые пояса в трех разных формах:
- Например, полное название часового пояса
America/New_York
. Распознанные имена часовых поясов перечислены вpg_timezone_names
представлении. PostgreSQL использует для этой цели широко используемые данные о часовых поясах IANA, поэтому те же имена часовых поясов распознаются и другим программным обеспечением. - Аббревиатура часового пояса, например
PST
. Такая спецификация просто определяет конкретное смещение от UTC, в отличие от полных имен часовых поясов, которые также могут подразумевать набор правил перехода на летнее время. Распознанные сокращения перечислены вpg_timezone_abbrevs
представлении. Вы не можете установить параметры конфигурации TimeZone или log_timezone в аббревиатуру часового пояса, но вы можете использовать аббревиатуры во входных значениях даты/времени и сAT TIME ZONE
оператором. - В дополнение к именам и аббревиатурам часовых поясов PostgreSQL будет принимать спецификации часовых поясов в стиле POSIX. Этот вариант обычно не предпочтительнее использования именованного часового пояса, но он может быть необходим, если нет подходящей записи IANA о часовом поясе.
Короче говоря, это разница между аббревиатурами и полными именами: аббревиатуры представляют собой определенное смещение от UTC, тогда как многие из полных имен подразумевают местное правило перехода на летнее время и, таким образом, имеют два возможных смещения UTC. Например, 2014-06-04 12:00 America/New_York
представляет полдень по местному времени в Нью-Йорке, которое для этой конкретной даты было восточным летним временем (UTC-4). Так 2014-06-04 12:00 EDT
указывает тот самый момент времени. Но 2014-06-04 12:00 EST
указывает полдень по стандартному восточному времени (UTC-5), независимо от того, действовало ли номинально летнее время в эту дату.
Чтобы усложнить ситуацию, некоторые юрисдикции использовали одну и ту же аббревиатуру часового пояса для обозначения разных смещений UTC в разное время; например, в Москве имелось в MSK
виду UTC+3 в некоторые годы и UTC+4 в другие. PostgreSQL интерпретирует такие аббревиатуры в соответствии с тем, что они значили (или совсем недавно значили) в указанную дату; но, как и в EST
приведенном выше примере, это не обязательно совпадает с местным гражданским временем в эту дату.
Интервальный ввод
interval
значения могут быть записаны с использованием следующего подробного синтаксиса:
[ @ ] [ ... ] [ ]
quantity unitquantity unitdirection
где quantity
число (возможно, подписанное); unit
является microsecond
, millisecond
, second
, minute
, hour
, day
, week
, month
, year
, decade
, century
, millennium
, или аббревиатурами или множественными числами этих единиц; direction
может быть ago
или пустым. Знак at ( @
) является необязательным шумом. Суммы различных единиц неявно добавляются с учетом соответствующего знака. ago
отрицает все поля. Этот синтаксис также используется для интервального вывода, если IntervalStyle имеет значение postgres_verbose
.
Количество дней, часов, минут и секунд можно указывать без явных обозначений единиц измерения. Например, '1 12:59:10'
читается так же, как '1 day 12 hours 59 min 10 sec'
. Также через тире можно указать комбинацию лет и месяцев; например '200-10'
читается так же, как '200 years 10 months'
. (Эти более короткие формы фактически единственные, разрешенные стандартом SQL , и используются для вывода, когда IntervalStyle
установлено значение sql_standard
.)
Abbreviation | Meaning |
---|---|
Y | Years |
M | Months (in the date part) |
W | Weeks |
D | Days |
H | Hours |
M | Minutes (in the time part) |
S | Seconds |