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的自动更新时间功能;

  1. 常见函数测试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;
  1. 函数解释
函数 参数类型 说明
FROM_UNIXTIME timestamp 时间戳 –> 日期格式
UNIX_TIMESTAMP datetime 日期格式 –> 时间戳
DATE_FORMAT (datetime , ‘%Y-%m-%d,%H:%i:%s’) 美化日期格式
YEAR datetime 获取日期格式的
MONTH datetime 获取日期格式的月份
DAY datetime 获取日期格式的日期
HOUR datetime 获取日期格式的小时
MINUTE datetime 获取日期格式的分钟
SECOND datetime 获取日期格式的
MICROSECOND datetime 获取日期格式的微秒
WEEK datetime 获取日期格式的第几周
QUARTER datetime 获取日期格式的季度
MONTHNAME datetime 获取日期格式的月份(英文)
NOW 获取当前时间2020-12-12 12:12:12
CURRENT_DATE 获取当前日期2020-12-12
CURRENT_TIME 获取当前时分秒12:12:12
DATE_ADD (datetime,INTERVAL expr unit) 增加n天 例:DATE_ADD(timestamp,INTERVAL 1 DAY)
DATE_SUB (datetime,INTERVAL expr unit) 减少n天
DATEDIFF (datetime1,datetime2) 两个时间间隔多少天

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

毫秒 季度
YEAR MONTH DAY HOUR MINTUE SECOND MIROSECOND WEEK QUATER
本作品采用《CC 协议》,转载必须注明作者和本文链接
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!