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)]

  char([M])

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

  postgres=# create domain year as int2 constraint ck check (value between 1901 and 2155);
  CREATE DOMAIN