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

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

Resources:

Https://www.cnblogs.com/IamThat/p/5111168.html

Https://www.cnblogs.com/stephen-liu74/archive/2012/04/30/2293602.html

Https://www.postgresql.org/docs/current/static/datatype-datetime.html

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