[Oracle|MySQL]数据库时间类型

来源：互联网发布：真丝枕套知乎编辑：程序博客网时间：2024/05/17 03:49

Oracle

SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64biPL/SQL Release 10.2.0.1.0 - ProductionCORE    10.2.0.1.0      ProductionTNS for Linux: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - Production

1，date

如果字符串格式和NLS_DATE_FROMAT参数设置的时间格式相同，则不需进行转换，否则需要使用to_date函数进行格式转换

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';Session altered.SQL> alter session set nls_date_language='american';Session altered.SQL> create table test(a date);Table created.SQL> insert into test values('2014-07-31 10:00:00');1 row created.SQL> insert into test values(to_date('201408','yyyymm'));1 row created.SQL> commit;Commit complete.SQL> select * from test;A-------------------2014-07-31 10:00:002014-08-01 00:00:00

2，timestamp

Oracle中，timestamp类型除了精度可以达到毫秒级别之外，和date类型没有区别

MySQL

mysql> select version();+-----------+| version() |+-----------+| 5.6.12    |+-----------+1 row in set

如果字符串格式和系统设置的时间格式相同，则不需进行转换，否则需要使用str_to_date函数进行格式转换

mysql> create table test(date datetime);Query OK, 0 rows affectedmysql> insert into test values('01-082014');1292 - Incorrect datetime value: '01-082014' for column 'date' at row 1mysql> insert into test values(str_to_date('01-082014','%d-%m%Y'));Query OK, 1 row affectedmysql> select * from test;+---------------------+| date                |+---------------------+| 2014-08-01 00:00:00 |+---------------------+1 row in set

1，year

2，date

mysql> show variables where Variable_name = 'date_format';+---------------+----------+| Variable_name | Value    |+---------------+----------+| date_format   | %Y-%m-%d |+---------------+----------+1 row in set

3，time

mysql> show variables where Variable_name = 'time_format';+---------------+----------+| Variable_name | Value    |+---------------+----------+| time_format   | %H:%i:%s |+---------------+----------+1 row in set

4，datetime

mysql> show variables where Variable_name = 'datetime_format';+-----------------+-------------------+| Variable_name   | Value             |+-----------------+-------------------+| datetime_format | %Y-%m-%d %H:%i:%s |+-----------------+-------------------+1 row in set

5，timestamp

mysql> create table test(a timestamp, b timestamp);Query OK, 0 rows affectedmysql> desc test;+-------+-----------+------+-----+---------------------+-----------------------------+| Field | Type      | Null | Key | Default             | Extra                       |+-------+-----------+------+-----+---------------------+-----------------------------+| a     | timestamp | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP || b     | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |+-------+-----------+------+-----+---------------------+-----------------------------+2 rows in set

表中的第一个timestamp类型字段设有默认值，并且如果更新时不指定该字段的值，该字段的值会自动更新为CURRENT_TIMESTAMP()的值

0 0