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
Filter rows that contain a search term, or set up advanced filters with multiple conditions. Quickly view rows from related tables, and save time by editing multiple rows at once. For analytics workloads, Postico has a powerful query editor with syntax highlighting and many advanced text editing features. But the best part of Postico is how well it works. Postico is made on a Mac for a Mac. It works great with all your other Mac apps. Use all the usual keyboard shortcuts. Postico gets the basic things like copy/paste just right, and also supports more advanced features like services for text editing. What’s New in Version 1.0.10.
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:
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.
The types
abstime and reltime are lower precision types which are used internally. You are discouraged from using these types in applications; these internal types might disappear in a future release.
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
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).
Table 8.10 shows some possible inputs for the
date type.
Table 8.10. Date Input
The time-of-day types are
time [ ( and time [ ( . time 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
Table 8.12. Time Zone Input
Refer to Section 8.5.3 for more information on how to specify time zones.
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:
and:
are valid values, which follow the ISO 8601 standard. In addition, the common format:
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,
![]()
is a
timestamp without time zone , while
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:
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 .
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
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 . The latter four accept an optional subsecond precision specification. (See Section 9.9.4.) Note that these are SQL functions and are not recognized in data input strings.
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
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
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.
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:
Postico 1 3 3 – A Modern Postgresql Client Settings
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:
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:
interval values can be written using the following verbose syntax:
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:
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
In the alternative format:
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.
In the verbose input format, and in some fields of the more compact input formats, field values can have fractional parts; for example
'1.5 week' or '01:02:03.45' . Such input is converted to the appropriate number of months, days, and seconds for storage. When this would result in a fractional number of months or days, the fraction is added to the lower-order fields using the conversion factors 1 month = 30 days and 1 day = 24 hours. For example, '1.5 month' becomes 1 month and 15 days. 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
Postico 1 3 3 – A Modern Postgresql Client Setting Tool
Internally
interval values are stored as months, days, and seconds. 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 seconds field can store fractions. Because intervals are usually created from constant strings or timestamp subtraction, this storage method works well in most cases, but can cause unexpected results:
Functions
justify_days and justify_hours are available for adjusting days and hours that overflow their normal ranges.
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
The character set support in PostgreSQL allows you to store text in a variety of character sets (also called encodings), including single-byte character sets such as the ISO 8859 series and multiple-byte character sets such as EUC (Extended Unix Code), UTF-8, and Mule internal code. All supported character sets can be used transparently by clients, but a few are not supported for use within the server (that is, as a server-side encoding). The default character set is selected while initializing your PostgreSQL database cluster using
initdb . It can be overridden when you create a database, so you can have multiple databases each with a different character set.
An important restriction, however, is that each database's character set must be compatible with the database's
LC_CTYPE (character classification) and LC_COLLATE (string sort order) locale settings. For C or POSIX locale, any character set is allowed, but for other libc-provided locales there is only one character set that will work correctly. (On Windows, however, UTF-8 encoding can be used with any locale.) If you have ICU support configured, ICU-provided locales can be used with most but not all server-side encodings.
Table 23.1 shows the character sets available for use in PostgreSQL.
Postico 1 3 3 – A Modern Postgresql Client Setting Example
Table 23.1. PostgreSQL Character Sets
Not all client APIs support all the listed character sets. For example, the PostgreSQL JDBC driver does not support
MULE_INTERNAL , LATIN6 , LATIN8 , and LATIN10 .
The
SQL_ASCII setting behaves considerably differently from the other settings. When the server character set is SQL_ASCII , the server interprets byte values 0–127 according to the ASCII standard, while byte values 128–255 are taken as uninterpreted characters. No encoding conversion will be done when the setting is SQL_ASCII . Thus, this setting is not so much a declaration that a specific encoding is in use, as a declaration of ignorance about the encoding. In most cases, if you are working with any non-ASCII data, it is unwise to use the SQL_ASCII setting because PostgreSQL will be unable to help you by converting or validating non-ASCII characters.
initdb defines the default character set (encoding) for a PostgreSQL cluster. For example,
sets the default character set to
EUC_JP (Extended Unix Code for Japanese). You can use --encoding instead of -E if you prefer longer option strings. If no -E or --encoding option is given, initdb attempts to determine the appropriate encoding to use based on the specified or default locale.
You can specify a non-default encoding at database creation time, provided that the encoding is compatible with the selected locale:
This will create a database named
korean that uses the character set EUC_KR , and locale ko_KR . Another way to accomplish this is to use this SQL command:
Notice that the above commands specify copying the
template0 database. When copying any other database, the encoding and locale settings cannot be changed from those of the source database, because that might result in corrupt data. For more information see Section 22.3.
The encoding for a database is stored in the system catalog
pg_database . You can see it by using the psql -l option or the l command.
Important
On most modern operating systems, PostgreSQL can determine which character set is implied by the
LC_CTYPE setting, and it will enforce that only the matching database encoding is used. On older systems it is your responsibility to ensure that you use the encoding expected by the locale you have selected. A mistake in this area is likely to lead to strange behavior of locale-dependent operations such as sorting.
PostgreSQL will allow superusers to create databases with
SQL_ASCII encoding even when LC_CTYPE is not C or POSIX . As noted above, SQL_ASCII does not enforce that the data stored in the database has any particular encoding, and so this choice poses risks of locale-dependent misbehavior. Using this combination of settings is deprecated and may someday be forbidden altogether.
23.3.3. Automatic Character Set Conversion Between Server and ClientPostico 1 3 3 – A Modern Postgresql Client Setting Permissions
PostgreSQL supports automatic character set conversion between server and client for many combinations of character sets (Section 23.3.4 shows which ones).
To enable automatic character set conversion, you have to tell PostgreSQL the character set (encoding) you would like to use in the client. There are several ways to accomplish this:
If the conversion of a particular character is not possible — suppose you chose
EUC_JP for the server and LATIN1 for the client, and some Japanese characters are returned that do not have a representation in LATIN1 — an error is reported.
If the client character set is defined as
SQL_ASCII , encoding conversion is disabled, regardless of the server's character set. (However, if the server's character set is not SQL_ASCII , the server will still check that incoming data is valid for that encoding; so the net effect is as though the client character set were the same as the server's.) Just as for the server, use of SQL_ASCII is unwise unless you are working with all-ASCII data.
PostgreSQL allows conversion between any two character sets for which a conversion function is listed in the
pg_conversion system catalog. PostgreSQL comes with some predefined conversions, as summarized in Table 23.2 and shown in more detail in Table 23.3. You can create a new conversion using the SQL command CREATE CONVERSION. (To be used for automatic client/server conversions, a conversion must be marked as “default” for its character set pair.)
Table 23.2. Built-in Client/Server Character Set Conversions
Table 23.3. All Built-in Character Set Conversions
These are good sources to start learning about various kinds of encoding systems.
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |