mysql 时区与时间函数

--在mysql中可以直接使用sleep()函数,sysdate函数返回的是执行到当前函数的时间,而now函数返回的是执行sql的时间

mysql> SELECT now(),SYSDATE(), SLEEP(2),now(), sysdate() from dual;

+---------------------+---------------------+----------+---------------------+---------------------+

| now()               | SYSDATE()           | SLEEP(2) | now()               | sysdate()           |

+---------------------+---------------------+----------+---------------------+---------------------+

| 2015-05-29 14:19:43 | 2015-05-29 14:19:43 |        0 | 2015-05-29 14:19:43 | 2015-05-29 14:19:45 |

+---------------------+---------------------+----------+---------------------+---------------------+

1 row in set (2.00 sec)

--在mysql中不能对日期直接进行加减,要使用date_add,date_sub函数

mysql> select now(),date_add(now(),interval 1 day) as tomorrow,date_sub(now(),interval 1 daY) as yerterday,date_sub(now(),interval -1 day) yesterday2

from dual;

+---------------------+---------------------+---------------------+---------------------+

| now()               | tomorrow            | yerterday           | yesterday2          |

+---------------------+---------------------+---------------------+---------------------+

| 2015-05-29 14:40:52 | 2015-05-30 14:40:52 | 2015-05-28 14:40:52 | 2015-05-30 14:40:52 |

+---------------------+---------------------+---------------------+---------------------+

1 row in set (0.00 sec)

--mysql中weekday从周一开始计算,但周一为0,dayofweek从周日开始计算,从1开始

mysql> select weekday(@a),dayofweek(@a),dayname(@a);

+-------------+---------------+-------------+

| weekday(@a) | dayofweek(@a) | dayname(@a) |

+-------------+---------------+-------------+

|           4 |             6 | Friday      |

+-------------+---------------+-------------+

--格式化日期函数,获取日期时间类型与timestamp的不同

mysql> select date_format('2015-10-01 23:59:59','%Y-%m-%d %H:%i:%s');

 select from_unixtime(date_format('2015-10-01 23:59:59','%Y-%m-%d %H:%i:%s'));

 select timestamp('2015-10-01 23:59:59');

 

 select date_format('2015-10-01 23:59:59','%Y-%m-%d %H:%i:%s') from dual where now()>date_format('2015-10-01 23:59:59','%Y-%m-%d %H:%i:%s');

 

 

--修改时区，则相应的时间函数会返回不同的值

This includes the values displayed by functions such as NOW() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns、

The current time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP() or values in DATE, TIME, or DATETIME columns

 

mysql> SELECT NOW();

+---------------------+

| NOW()               |

+---------------------+

| 2016-02-03 09:46:33 |

+---------------------+

1 row in set (0.00 sec)

 

mysql> set time_zone='+07:00';

Query OK, 0 rows affected (0.00 sec)

--时间改变

mysql> SELECT NOW();

+---------------------+

| NOW()               |

+---------------------+

| 2016-02-03 08:46:35 |

+---------------------+

1 row in set (0.00 sec)

--将一个时区的时间转换成另一个时区的时间

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');

+-----------------------------------------------------+

| CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00') |

+-----------------------------------------------------+

| 2004-01-01 22:00:00                                 |

+-----------------------------------------------------+

1 row in set (0.00 sec)

--如果以下查询结果是null，则时区信息表里数据为空

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');

+-----------------------------------------------+

| CONVERT_TZ('2004-01-01 12:00:00','GMT','MET') |

+-----------------------------------------------+

| 2004-01-01 13:00:00                           |

+-----------------------------------------------+

1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM mysql.time_zone_name;

+----------+

| COUNT(*) |

+----------+

|        0 |

+----------+

1 row in set (0.00 sec)

--导入时区信息到数据库表中

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p123456 mysql

--查看数据库的时区，如果 time_zone = SYSTEM ，则数据库的时区与操作系统相关

The operating system time affects the value that the MySQL server uses for times if its time zone is set to SYSTEM

mysql> show variables like '%time_zone%';

+------------------+--------+

| Variable_name    | Value  |

+------------------+--------+

| system_time_zone | CST    |

| time_zone        | SYSTEM |

+------------------+--------+

-- lc_time_names 变量控制着星期，月，日的显示方式

The locale indicated by the lc_time_names system variable controls the language used to display day and month names and abbreviations. 

This variable affects the output from the DATE_FORMAT(),DAYNAME(), and MONTHNAME() functions.

lc_time_names does not affect the STR_TO_DATE() or GET_FORMAT() function

mysql> SELECT @@lc_time_names;

+-----------------+

| @@lc_time_names |

+-----------------+

| en_US           |

+-----------------+

1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT('2010-01-01','%W %a %M %b');

+-----------------------------------------+

| DATE_FORMAT('2010-01-01','%W %a %M %b') |

+-----------------------------------------+

| Friday Fri January Jan                  |

+-----------------------------------------+

1 row in set (0.00 sec)