mysql时间函数

(1)日期类型

     
日期类型日期格式存储空间日期范围datetime YYYY-MM-DD HH:mm:ss8bytes1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 timestampYYYY-MM-DD HH:mm:ss4bytes1970-01-01 00:00:01 ~ 2038 dateYYYY-MM-DD3bytes1000-01-01          ~ 9999-12-31 timeHH:mm:ss3bytes-838:59:59          ~ 838:59:59yearYYYY1bytes 1901                ~ 2155

        1.datetime与timestamp

           1)datetime的日期范围比较大,timestamp容器出现超出的情况

           2)timestamp在默认情况下,在insert\update数据时,timestamp列会自动以当前时间填充

           建表代码如下:

create table t8 (  `id1` timestamp NOT NULL default CURRENT_TIMESTAMP,  `id2` datetime default NULL);

         推荐使用datetime

        2.time类型的范围为什么可以有负数

           为了方便日期相减

(2)当前日期函数

        1.获取当前日期时间:now()

mysql> select now();+---------------------+ | now()               | +---------------------+ | 2008-08-08 22:20:46 | +---------------------+

        2.获取当前日期:curdate()

mysql> select curdate();+------------+ | curdate() | +------------+ | 2008-08-08 | +------------+

        3.获取当前时间:curtime()

mysql> select curtime();+-----------+ | curtime() | +-----------+ | 22:41:30 | +-----------+

        4.获取当前UTC时间:utc_timestamp()

mysql> select utc_timestamp(), utc_date(), utc_time(), now()+---------------------+------------+------------+---------------------+ | utc_timestamp()     | utc_date() | utc_time() | now()               | +---------------------+------------+------------+---------------------+ | 2008-08-08 14:47:11 | 2008-08-08 | 14:47:11   | 2008-08-08 22:47:11 | +---------------------+------------+------------+---------------------+

          因为我国位于东八区,所以时间就是utc事件+8小时

(3)timestamp函数(时间+或-或转换)

       1.date类型向timestamp转换

          timestamp(date)           //date to timestamp

       2.时间相加

          1)根据表达式

select timestamp('2008-08-08 08:00:00', '01:01:01');    -- 2008-08-08 09:01:01 select timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-18 09:01:01

          2)根据timestampadd()

timestampadd(unit,interval,datetime_expr)

             eg

select timestampadd(day, 1, '2008-08-08 08:00:00');     -- 2008-08-09 08:00:00 

       3.时间相减

          根据时间单位决定结果

select timestampdiff(year,'2002-05-01','2001-01-01');                    -- -1 select timestampdiff(day ,'2002-05-01','2001-01-01');                    -- -485 select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); -- -12

(4)时间单位选取

       取哪个字段用什么单位即可

set @dt = '2008-09-10 07:15:30.123456';select extract(year                from @dt); -- 2008 select extract(quarter             from @dt); -- 3 select extract(month               from @dt); -- 9 select extract(week                from @dt); -- 36 select extract(day                 from @dt); -- 10 select extract(hour                from @dt); -- 7 select extract(minute              from @dt); -- 15 select extract(second              from @dt); -- 30 select extract(microsecond         from @dt); -- 123456select extract(year_month          from @dt); -- 200809 select extract(day_hour            from @dt); -- 1007 select extract(day_minute          from @dt); -- 100715 select extract(day_second          from @dt); -- 10071530 select extract(day_microsecond     from @dt); -- 10071530123456 select extract(hour_minute         from @dt); -- 715 select extract(hour_second         from @dt); -- 71530 select extract(hour_microsecond    from @dt); -- 71530123456 select extract(minute_second       from @dt); -- 1530 select extract(minute_microsecond  from @dt); -- 1530123456 select extract(second_microsecond  from @dt); -- 30123456

        注意:比如上面的day_second不是说只选取day和second字段,而是从day到second

(5)返回日期在一周一月一年中的位置

set @dt = '2008-08-08';select dayofweek(@dt);   -- 6 select dayofmonth(@dt); -- 8 select dayofyear(@dt);   -- 221

        注意Sunday才是第一天

(6)返回日期属于这一年的第多少周

set @dt = '2008-08-08';select weekofyear(@dt); -- 32

(7)返回月份中的最后一天

select last_day('2008-02-01'); -- 2008-02-29 

       last_day函数一个非常重要的作用是算出指定日期的月份有多少天

select now(),extract(day from last_day(now()))

        即从last day中抽取出day字段

(8)时间与字符串相互转换

          1.str_to_date

             能够将各种则乱格式的字符串转换为日期格式

select str_to_date('08/09/2008', '%m/%d/%Y');                   -- 2008-08-09 select str_to_date('08/09/08' , '%m/%d/%y');                   -- 2008-08-09 select str_to_date('08.09.2008', '%m.%d.%Y');                   -- 2008-08-09 select str_to_date('08:09:30', '%h:%i:%s');                     -- 08:09:30 select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30

          2.date_format

             能够将时间类型转化为指定的字符串格式

mysql> select date_format('2008-08-08 22:23:01', '%Y/%m/%d %H/%i/%s');+----------------------------------------------------+ | date_format('2008-08-08 22:23:01', '%Y/%m/%d %H/%i/%s');| +----------------------------------------------------+ | 2008/08/08 22/23/01                                   |+----------------------------------------------------+

mysql> select time_format('22:23:01', '%H.%i.%s');+-------------------------------------+ | time_format('22:23:01', '%H.%i.%s') | +-------------------------------------+ | 22.23.01                            | +-------------------------------------+