技术头条 - 一个快速在微博传播文章的方式     搜索本站
您现在的位置首页 --> MySQL --> Mysql时间函数

Mysql时间函数

浏览:1138次  出处信息

    --返回当前时间

    mysql> select curdate(),curtime(),now(),DATE(now()),sysdate();

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

    | curdate() | curtime() | now() | DATE(now()) | sysdate() |

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

    | 2008-12-02 | 10:11:36 | 2008-12-02 10:11:36 | 2008-12-02 | 2008-12-02 10:11:36 |

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

    1 row in set (0.00 sec)

    mysql> SELECT CURDATE(),CURDATE()+0,CURTIME(),CURTIME()+0;

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

    | CURDATE() | CURDATE()+0 | CURTIME() | CURTIME()+0 |

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

    | 2008-12-02 | 20081202 | 10:00:33 | 100033.000000 |

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

    1 row in set (0.00 sec)

    --返回日期当月最后一天

    mysql> select last_day(\'2008-12-02\');

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

    | last_day(\'2008-12-02\') |

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

    | 2008-12-31 |

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

    1 row in set (0.00 sec)

    --返回日期的星期几

    mysql> select dayname(\'2008-12-02\'),dayofweek(\'2008-12-02\');

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

    | dayname(\'2008-12-02\') | dayofweek(\'2008-12-02\') |

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

    | Tuesday | 3 |

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

    1 row in set (0.00 sec)

    --返回日期的年,月,日

    mysql> select month(\'2008-12-02\'),year(\'2008-12-02\'),day(\'2008-12-02\');

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

    | month(\'2008-12-02\') | year(\'2008-12-02\') | day(\'2008-12-02\') |

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

    | 12 | 2008 | 2 |

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

    1 row in set (0.00 sec)

    --返回日期的小时,分,秒

    mysql> SELECT HOUR(\'10:05:03\'),MINUTE(\'10:05:03\'),SECOND(\'10:05:03\');

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

    | HOUR(\'10:05:03\') | MINUTE(\'10:05:03\') | SECOND(\'10:05:03\') |

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

    | 10 | 5 | 3 |

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

    1 row in set (0.00 sec)

    1.SUBDATE(d,t):起始时间加上一段时间

    --返回起始时间加上N天

    mysql> SELECT DATE_ADD(\'1998-01-02\', INTERVAL 31 DAY),ADDDATE(\'1998-01-02\', 31);

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

    | DATE_ADD(\'1998-01-02\', INTERVAL 31 DAY) | ADDDATE(\'1998-01-02\', 31) |

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

    | 1998-02-02 | 1998-02-02 |

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

    1 row in set (0.00 sec)

    --返回起始时间加上年,月

    mysql> SELECT DATE_ADD(\'1998-01-02\', INTERVAL 2 YEAR), DATE_ADD(\'1998-01-02\', INTERVAL 2 MONTH);

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

    | DATE_ADD(\'1998-01-02\', INTERVAL 2 YEAR) | DATE_ADD(\'1998-01-02\', INTERVAL 2 MONTH) |

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

    | 2000-01-02 | 1998-03-02 |

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

    1 row in set (0.00 sec)

    --返回起始时间加上小时,加上分钟

    mysql> SELECT DATE_ADD(\'1998-01-02\', INTERVAL 2 hour), DATE_ADD(\'1998-01-02\', INTERVAL 2 minute);

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

    | DATE_ADD(\'1998-01-02\', INTERVAL 2 hour) | DATE_ADD(\'1998-01-02\', INTERVAL 2 minute) |

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

    | 1998-01-02 02:00:00 | 1998-01-02 00:02:00 |

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

    1 row in set (0.00 sec)

    2.SUBDATE(d,t):起始时间减去一段时间

    mysql> SELECT SUBDATE(\'1998-01-02\', INTERVAL 31 DAY),SUBDATE(\'1998-01-02\', 31);

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

    | SUBDATE(\'1998-01-02\', INTERVAL 31 DAY) | SUBDATE(\'1998-01-02\', 31) |

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

    | 1997-12-02 | 1997-12-02 |

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

    1 row in set (0.00 sec)

    3.ADDTIME(d,t):起始时间d加入时间t

    mysql> SELECT ADDTIME(\'1997-12-31 23:59:50\',\'00:00:05\'), ADDTIME(\'23:59:50\',\'00:00:05\') ;

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

    | ADDTIME(\'1997-12-31 23:59:50\',\'00:00:05\') | ADDTIME(\'23:59:50\',\'00:00:05\') |

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

    | 1997-12-31 23:59:55 | 23:59:55 |

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

    1 row in set (0.00 sec)

    4.SUBTIME(d,t):起始时间d减去时间t

    mysql> SELECT SUBTIME(\'1997-12-31 23:59:50\',\'00:00:05\'), SUBTIME(\'23:59:50\',\'00:00:05\');

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

    | SUBTIME(\'1997-12-31 23:59:50\',\'00:00:05\') | SUBTIME(\'23:59:50\',\'00:00:05\') |

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

    | 1997-12-31 23:59:45 | 23:59:45 |

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

    1 row in set (0.00 sec)

    5.DATEDIFF(d1,d2):返回起始时间d1和结束时间d2之间的天数

    mysql> SELECT DATEDIFF(\'1997-12-31 23:59:59\',\'1997-12-30\');

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

    | DATEDIFF(\'1997-12-31 23:59:59\',\'1997-12-30\') |

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

    | 1 |

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

    1 row in set (0.00 sec)

    6.DATE_FORMAT(date,format):根据format字符串显示date值的格式

    mysql> SELECT DATE_FORMAT(\'2008-12-02 22:23:00\', \'%Y %m %m %H:%i:%s\');

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

    | DATE_FORMAT(\'2008-12-02 22:23:00\', \'%Y %m %m %H:%i:%s\') |

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

    | 2008 12 12 22:23:00 |

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

    1 row in set (0.00 sec)

    7.STR_TO_DATE(str,format) 字符串转化为时间

    mysql> SELECT STR_TO_DATE(\'04/31/2004\', \'%m/%d/%Y %H:%i:s\');

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

    | STR_TO_DATE(\'04/31/2004\', \'%m/%d/%Y %H:%i:s\') |

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

    | 2004-04-31 00:00:00 |

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

    1 row in set (0.00 sec)

    8.TIMESTAMP(expr) , TIMESTAMP(expr,expr2) :

    对于一个单参数,该函数将日期或日期时间表达式 expr 作为日期时间值返回.对于两个参数, 它将时间表达式 expr2添加到日期或日期时间表达式 expr 中,将theresult作为日期时间值返回

    mysql> SELECT TIMESTAMP(\'2003-12-31\'), TIMESTAMP(\'2003-12-31 12:00:00\',\'12:00:00\');

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

    | TIMESTAMP(\'2003-12-31\') | TIMESTAMP(\'2003-12-31 12:00:00\',\'12:00:00\') |

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

    | 2003-12-31 00:00:00 | 2004-01-01 00:00:00 |

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

    1 row in set (0.00 sec)

    --取当天0点0分,下一天0点0分

    mysql> select timestamp(date(sysdate())),timestamp(adddate(date(sysdate()),1));

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

    | timestamp(date(sysdate())) | timestamp(adddate(date(sysdate()),1)) |

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

    | 2008-12-02 00:00:00 | 2008-12-03 00:00:00 |

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

    1 row in set (0.00 sec)

     时间函数在程序中应用非常广泛,基本上每个程序都会用到,都会和时间打交道,稍微总结了一下,这样可以减少每次去查文档的时间。

QQ技术交流群:445447336,欢迎加入!
扫一扫订阅我的微信号:IT技术博客大学习
© 2009 - 2024 by blogread.cn 微博:@IT技术博客大学习

京ICP备15002552号-1