Issues with date, time, and timestamp in the database (not yet continued)

Source: Internet
Author: User
Tags time zones iso 8601 numeric value postgresql

Recently imported data in PostgreSQL, encountering time problems, combing the time in the database:

1. Basic Concepts

1.1 Date: That is the commonly said year, month, and day, the following is a list of valid date formats:

in YMD mode
Example description
January 8, 1999 No ambiguity in any Datestyle input mode
1999-01-08 ISO-8601 format, in any way, January 8, 1999, (recommended format)
1/8/1999 ambiguity, under MDY is January 8; read in dmy mode August 1
1/18/1999 read in mdy mode January 18, other modes denied
01/02/03 mdy mode, January 2, 2003, February 1, 2003 in DMY mode, February 3, 2001
1999-jan-08 any mode is January 8
jan-08-1999 any mode is January 8
08-jan-1999 any mode is January 8
99-jan-08 is January 8 in YMD mode, otherwise error
08-jan-99 January 8 except for errors in YMD mode
jan-08-99 January 8 except for errors in YMD mode
19990108 ISO-8601; all modes are January 8, 1999
990108 ISO-8601; 1999 in any mode January 8

1.2 Time: the moment of the day, in hours, minutes, seconds, which can be followed by an optional time zone, the following is a list of valid time formats:

Example Describe
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 the value
04:05 PM Same as 16:05; input hours must be <= 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 With an abbreviated time zone
2003-04-12 04:05:06 America/new_york Time zone with full name

1.3 Timestamp (timestamp): consists of a join of a date and time, followed by an optional time zone. The following two ways are valid:

(1) 1999-01-08 04:05:06

(2) 1999-01-08 04:05:06-8:00

1.4 Time Zone:

Example Describe
Pst PST (Pacific Standard Time)
America/new_york Time Zone full Name
Pst8pdt POSIX-style time zone name
-8:00 ISO-8601 vs. PST offset
-800 ISO-8601 vs. PST offset
-8 ISO-8601 vs. PST offset
Zulu Military abbreviation for UTC (may be American)
Z Zulu's abbreviation

Questions to keep in mind about time zones:

(1) The SQL standard distinguishes whether the type of constants is timestamp without time zone or timestamp with time zoneby viewing the presence of the symbol "+" or "-". For example, TIMESTAMP ' 2004-10-19 10:23:54 ' type TIMESTAMP without time zone,TIMESTAMP ' 2004-10-19 10:23:54+02 ' The type of timestamp with time zone. PostgreSQL does not use this rule, so examples from the previous two examples will be considered timestamp without time zone. In PostgreSQL, the constants of thetimestamp without time zone type must precede the timestamp with time zone, for example, timestamp with time ZONE ' 2004-10-19 10:23:54+02 '.     

(2) the time zone information is automatically ignored by the system timestamp without time zone type constants.     

(3) the internal storage format for data of the timestamp with time zone type is always UTC (Global unification Time, formerly known as Greenwich Mean GMT). If a time zone is specified in an input value, it is converted to UTC based on that time zone, and if no declaration is specified in the input value, the system converts it to UTC format, based on the value of the parameter timezone as the specified time zone.  

(4) If you want to output a timestamp with time zone type of data , it is always transferred from UTC to the time zone specified by the parameter timezone, and is displayed as local times for that time zone. To see that time in another time zone, either modify the value of the parameter parameter timezone, or use the at Timing ZONE clause.  

(5) Conversion between timestamp without time zone and timestamp with time zone is usually assumed timestamp without time zone the time zone of the numeric value is the time zone specified by the parameter timezone . You can use the at time zone to specify a different timezone.

1.5 interval (interval): (Note: This part of the understanding is not very good, needs to be perfected)

interval the value of a type can be defined using the following syntax:

[@] Quantity Unit [quantity unit...] [Direction]

Here quantity is a number (possibly signed),unit is microsecond, millisecond,Second ,minute,hour, day, week,month, year,decade ,century,Millennium , or abbreviations or complex numbers of these units,direction can be either ago or empty. The symbol "@" is optional and may not be written.

The number of days, hours, minutes, and seconds can be followed without explicitly following the unit. For example, the"1 12:59:10" and the "1 day hours min Ten sec" are equivalent. The optional precision p value is between 0 and 6, and the default is the precision of the input constant.

2 Date/time types supported in PostgreSQL

PostgreSQL supports all date and time types in the SQL standard as follows:

Name Storage space Size Describe Minimum value Maximum Value Resolution
Date 4 bytes Only Date 4713 BC (A.D. 4713) 5874897 AD (Gregorian ERA) 1 days
Time [(P)] [without time zone] 8 bytes Only time 00:00:00 24:00:00 1 μs/14 bit
Time [(P)] with time zone bytes Only time, with time zone 00:00:00+1459 24:00:00-1459 1 μs/14 bit
timestamp [(p)] [without time zone] 8 bytes Include Date and time 4713 BC 294276 AD 1 μs/14 bit
timestamp [(P)] with time zone 8 bytes Includes date and time, with time zone 4713 BC 294276 AD 1 μs/14 bit
interval [(p)] bytes Time interval -178 million years 178 million years 1 μs/14 bit





Issues with date, time, and timestamp in the database (not yet continued)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.