首页 » Mysql日期与时间

Mysql日期与时间

Mysql日期与时间的几个基本类型:

数据类型数据格式有效值
date'YYYY-MM-DD''1000-01-01' - '9999-12-31'
datetime'YYYY-MM-DD HH:MM:SS''1000-01-01 00:00:00' - '9999-12-31 23:59:59'
timestamp'YYYY-MM-DD HH:MM:SS''1970-01-01 00:00:00' UTC - '2038-01-19 03:14:07' UTC

时区

查看时区:

mysql> select @@global.time_zone,@@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.00 sec)

全局系统变量time_zone表示服务器当前使用的时区,初始值为SYSTEM,表示与系统时区相同。可用--default-time-zone=TZ选项显示指定初始值。

设置时区的几种方式:

mysql> set time_zone='+8:00';
Query OK, 0 rows affected (0.00 sec)
mysql> set time_zone='Asia/Shanghai';
ERROR 1298 (HY000): Unknown or incorrect time zone: 'Asia/Shanghai'

不能识别Asia/Shanghai,因此先用mysql_tzinfo_to_sql脚本导入时区文件:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uroot mysql -p

再次设置时区(只对当前session有效):

mysql> set time_zone='Asia/Shanghai';
Query OK, 0 rows affected (0.01 sec)

登录一台服务器,检查并设置全局时区(用global):

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2012-12-15 07:52:02 |
+---------------------+
1 row in set (0.00 sec)

mysql> set global time_zone='+8:00';      
Query OK, 0 rows affected (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2012-12-15 20:52:19 |
+---------------------+
1 row in set (0.00 sec)

分享

0