PostgreSQL MySQL 兼容性之 - 时间类型
背景
时间类型兼容性。
DATE
MySQL
DATE
A date. The supported range is '1000-01-01' to '9999-12-31'.
'0000-00-00' is a permitted special value (zero-date), unless the NO_ZERO_DATE SQL_MODE is used.
Also, individual components of a date can be set to 0 (for example: '2015-00-12'), unless the NO_ZERO_IN_DATE SQL_MODE is used.
PostgreSQL
DATE
但是PG不支持'0000-00-00', 通过改源码 ValidateDate, 自动将'0000-00-00'转存为'0001-01-01 BC'
TIME
MySQL
TIME [(<microsecond precision>)]
A time. The range is '-838:59:59.999999' to '838:59:59.999999'. Microsecond precision can be from 0-6; if not specified 0 is used.
PostgreSQL
TIME [(<microsecond precision>)]
DATETIME
MySQL
DATETIME [(microsecond precision)]
A date and time combination. The supported range is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'.
MariaDB displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format, but allows assignment of values to DATETIME columns using either strings or numbers.
'0000-00-00' is a permitted special value (zero-date), unless the NO_ZERO_DATE SQL_MODE is used.
Also, individual components of a date can be set to 0 (for example: '2015-00-12'), unless the NO_ZERO_IN_DATE SQL_MODE is used.
In many cases, the result of en expression involving a zero-date, or a date with zero-parts, is NULL.
If the ALLOW_INVALID_DATES SQL_MODE is enabled, if the day part is in the range between 1 and 31, the date does not produce any error, even for months that have less than 31 days.
PostgreSQL
timestamp [(microsecond precision)]
timestamptz [(microsecond precision)]
TIMESTAMP
MySQL
TIMESTAMP [(<microsecond precision)]
A timestamp in the format YYYY-MM-DD HH:MM:DD.
The timestamp field is generally used to define at which moment in time a row was added or updated and by default will automatically be assigned the current datetime when a record is inserted or updated.
PostgreSQL
timestamp [(microsecond precision)]
timestamptz [(microsecond precision)]
YEAR
MySQL
YEAR[(4)]
A year in two-digit or four-digit format. The default is four-digit format. Note that the two-digit format has been deprecated since 5.5.27.
In four-digit format, the allowable values are 1901 to 2155, and 0000.
PostgreSQL
通过自定义域实现year类型
postgres=# create domain year as int2 constraint ck check (value between 1901 and 2155);
CREATE DOMAIN