作者
digoal
日期
2016-03-15
标签
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
```
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
