PostgreSQL MySQL 兼容性之 - 时间类型

1 minute read

背景

时间类型兼容性。

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  

Flag Counter

digoal’s 大量PostgreSQL文章入口