Mysql日期常用语句

数据类型

1. datetime
2. timestamp

语句测试

1. 建表

   DROP TABLE IF EXISTS test_time;
   CREATE TABLE test_time (
    `id` INT (3) UNSIGNED AUTO_INCREMENT,
    `name` char(5) DEFAULT '',  //用于更新时间测试
    `test_datetime` datetime DEFAULT CURRENT_TIMESTAMP,
    `test_datetime1` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `test_timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `test_timestamp1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `int_timestamp` INT NOT NULL DEFAULT 1577808000,
    PRIMARY KEY (`id`)
   );

2. 插入数据

   INSERT into test_time(name) VALUE('huang');

   因为建表时已经使用了 DEFAULT CURRENT_TIMESTAMP 所以插入数据后会自动创建当前时间

   UPDATE test_time set name='hui' WHERE name='huang';

   测试test_datetime1,test_timestamp1的自动更新时间功能;

3. 常见函数测试SQL

   SELECT 
   test_datetime,
    DATE_FORMAT(test_datetime,'%Y-%m-%d'),
    FROM_UNIXTIME(int_timestamp) as int_day,
    YEAR(test_datetime) as year,
    MONTH(test_datetime) as month,
    DAY(test_datetime) as day,
    HOUR(test_datetime) as hour,
    minute(test_datetime) as minute,
    second(test_datetime) as second,
    MICROSECOND(test_datetime) as mic_second,
    WEEK(test_datetime) as week,
    QUARTER(test_datetime) as quarter,
    MONTHNAME(test_datetime) as month_name,
    NOW() as now,
    CURRENT_date() as cur_date,
    CURRENT_TIME() as cur_time,
    DATE_ADD(NOW(),INTERVAL 1 DAY) as add_day,
    DATE_SUB(NOW(),INTERVAL -1 DAY) as sub_day,
    DATEDIFF(NOW(),FROM_UNIXTIME(int_timestamp)) as diff
   FROM test_time;

4. 函数解释

-上述DATE_ADD/DATE_SUB中unit的参数可为:

本作品采用《CC 协议》，转载必须注明作者和本文链接