MySQL 时间函数

jopen 13年前
     <p>mysql 时间函数</p>    <pre>mysql> select now(), current_date(), current_time(), unix_timestamp(); +---------------------+----------------+----------------+------------------+ | now()               | current_date() | current_time() | unix_timestamp() | +---------------------+----------------+----------------+------------------+ | 2010-04-13 10:32:04 | 2010-04-13     | 10:32:04       |       1271125924 | +---------------------+----------------+----------------+------------------+</pre>    <pre>mysql> select FROM_UNIXTIME(1271125924), unix_timestamp('2010-04-13 10:32:04'); +---------------------------+---------------------------------------+ | FROM_UNIXTIME(1271125924) | unix_timestamp('2010-04-13 10:32:04') | +---------------------------+---------------------------------------+ | 2010-04-13 10:32:04       |                            1271125924 | +---------------------------+---------------------------------------+</pre>    <pre>mysql> select TO_DAYS('2010-04-13 22:33:11'), from_days(734240); +--------------------------------+-------------------+ | TO_DAYS('2010-04-13 22:33:11') | from_days(734240) | +--------------------------------+-------------------+ |                         734240 | 2010-04-13        | +--------------------------------+-------------------+</pre>    <pre>year('2010-04-13 22:33:11'): 2010         month('2010-04-13'): 4     monthname('2010-04-13'): April    dayofmonth('2010-04-13'): 13     dayofyear('2010-04-13'): 103     dayofweek('2010-04-13'): 3       weekday('2010-04-13'): 1 hour('2010-04-13 22:33:11'): 22          minute('22:33:11'): 33          second('22:33:11'): 11 1 row in set (0.00 sec)</pre>    <p>注:2010-04-13是星期二</p>    <pre>                      "1997-12-31 23:59:59" + INTERVAL 1 SECOND: 1998-01-01 00:00:00                                   INTERVAL 1 DAY + "1997-12-31": 1998-01-01                                "1998-01-01" - INTERVAL 1 SECOND: 1997-12-31 23:59:59              DATE_ADD("1997-12-31 23:59:59", INTERVAL 1 SECOND): 1998-01-01 00:00:00                DATE_ADD("1997-12-31 23:59:59",  INTERVAL 1 DAY): 1998-01-01 23:59:59  DATE_ADD("1997-12-31 23:59:59",  INTERVAL "1:1" MINUTE_SECOND): 1998-01-01 00:01:00 DATE_SUB("1998-01-01 00:00:00",  INTERVAL "1 1:1:1" DAY_SECOND): 1997-12-30 22:58:59     DATE_ADD("1998-01-01 00:00:00",  INTERVAL "-1 10" DAY_HOUR): 1997-12-30 14:00:00                         DATE_SUB("1998-01-02", INTERVAL 31 DAY): 1997-12-02                                 EXTRACT(YEAR FROM "1999-07-02"): 1999                  EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03"): 199907                  EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03"): 20102</pre>