时间与日期

时间与日期类型

常被使用的是 DATE、DATETIME 和 TIMESTAMP 类型，其表示的范围为：

  • DATE：’1000-01-01’ to ‘9999-12-31’
  • DATETIME：’1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’
  • TIMESTAMP：’1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC

TIMESTAMP 存储的范围比 DATETIME 要小，但是空间利用率也最高。MySQL 支持的时间精度最高为 1s，如果更精确的存储，就必须自己定义存储格式了。

获取函数

now 与 sysdate 是常见的获取日期的函数，不过 now() 在执行开始时值就得到了，sysdate() 在函数执行时动态得到值。

--- 获得当前日期+时间（date + time）
mysql> select now();

--- sysdate() 日期时间函数，一般情况下很少用到。
mysql > select now(), sleep(3), now(); -- 2019-06-25 14:54:17	0	2019-06-25 14:54:17

--- 获得当前时间戳
mysql> select current_timestamp, current_timestamp();

转换函数

MySQL 日期、时间转换函数：date_format(date,format), time_format(time,format) 能够把一个日期/时间转换成各种各样的字符串格式。它是 str_to_date(str,format) 函数的 一个逆转换。

--- 日期/时间转换为字符串
mysql> select date_format('2018-08-08 22:23:01', '%Y%m%d%H%i%s');

--- 转化为日期对象
mysql> select str_to_date('08/09/2018', '%m/%d/%Y'); -- 2018-08-09
mysql> select str_to_date('08/09/18', '%m/%d/%y'); -- 2018-08-09
mysql> select str_to_date('08.09.2018', '%m.%d.%Y'); -- 2018-08-09
mysql> select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30
mysql> select str_to_date('08.09.2018 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2018-08-09 08:09:30

--- 日期、天数转换函数
mysql> select to_days('0000-00-00'); -- 0
mysql> select to_days('2018-08-08'); -- 737279

--- 时间、秒转换函数
mysql> select time_to_sec('01:00:05'); -- 3605
mysql> select sec_to_time(3605); -- '01:00:05'

--- 拼凑日期、时间函数
mysql> select makedate(2001,31); -- '2001-01-31'
mysql> select makedate(2001,32); -- '2001-02-01'
mysql> select maketime(12,15,30); -- '12:15:30'

--- Unix 时间戳、日期转换函数
mysql> select unix_timestamp(); -- 1218290027
mysql> select unix_timestamp('2008-08-08'); -- 1218124800
mysql> select unix_timestamp('2008-08-08 12:30:00'); -- 1218169800

mysql> select from_unixtime(1218290027); -- '2008-08-09 21:53:47'
mysql> select from_unixtime(1218124800); -- '2008-08-08 00:00:00'
mysql> select from_unixtime(1218169800); -- '2008-08-08 12:30:00'

mysql> select from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x'); -- '2008 8th August 12:30:00 2008'

计算函数

-- 为日期增加一个时间间隔
mysql> set @dt = now();

mysql> select date_add(@dt, interval 1 day); -- add 1 day
mysql> select date_add(@dt, interval 1 hour); -- add 1 hour
mysql> select date_add(@dt, interval 1 minute); -- ...
mysql> select date_add(@dt, interval 1 second);
mysql> select date_add(@dt, interval 1 microsecond);
mysql> select date_add(@dt, interval 1 week);
mysql> select date_add(@dt, interval 1 month);
mysql> select date_add(@dt, interval 1 quarter);
mysql> select date_add(@dt, interval 1 year);

select date_add(@dt, interval -1 day); -- sub 1 day

MySQL 中时间戳同样支持计算：

select timestamp('2008-08-08'); -- 2008-08-08 00:00:00
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

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

-- MySQL timestampadd() 函数类似于 date_add()。
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

select datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00'); -- 7

时区转换

时区转换也可以通过 date_add, date_sub, timestampadd 来实现。

convert_tz(dt,from_tz,to_tz)

select convert_tz('2008-08-08 12:00:00', '+08:00', '+00:00'); -- 2008-08-08 04:00:00

select date_add('2008-08-08 12:00:00', interval -8 hour); -- 2008-08-08 04:00:00
select date_sub('2008-08-08 12:00:00', interval 8 hour); -- 2008-08-08 04:00:00
select timestampadd(hour, -8, '2008-08-08 12:00:00'); -- 2008-08-08 04:00:00

时间段查询

-- 今天
select * from 表名 where to_days(时间字段名) = to_days(now());
-- 昨天
SELECT * FROM 表名 WHERE TO_DAYS(NOW()) - TO_DAYS(时间字段名) <= 1
-- 7天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)
-- 近30天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)
-- 本月
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ), '%Y%m' )
-- 上一月
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ), '%Y%m' ), date_format( 时间字段名, '%Y%m' ) ) =1
-- 查询本季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());
-- 查询上季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
-- 查询本年数据
select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());
-- 查询上年数据
select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));
-- 查询当前这周的数据
SELECT name, submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());
-- 查询上周的数据
SELECT name, submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;
-- 查询当前月份的数据
select name, submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')
-- 查询距离当前现在6个月的数据
select name, submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();
-- 查询上个月的数据
select name, submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')

select * from user where DATE_FORMAT(pudate, ' %Y%m ' ) = DATE_FORMAT(CURDATE(), ' %Y%m ' ) ;
select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now())
select * from user where MONTH (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = MONTH (now())
select * from [user] where YEAR(FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = YEAR (now())
and MONTH (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = MONTH (now())
select * from [user] where pudate between 上月最后一天 and 下月第一天  where date(regdate) = curdate();
select * from test where year(regdate)=year(now()) and month(regdate)=month(now()) and day(regdate)=day(now())
SELECT date( c_instime ) ,curdate( ) FROM `t_score`  WHERE 1  LIMIT 0, 30；

JDBC 时区

在应用开发中，往往也会出现应用时区与 MySQL 服务器时区不一致的情况，当数据库为 UTC 时区时，我们的 JDBC serverTimezone 参数也应该设置为 UTC 以保证时区的一致性。并且需要注意的是，datetime 往往写入的就是字面值，在查询时候给出的也是字面值而未根据客户端的时区进行转化。

long nowTs = System.currentTimeMillis();
LocalDate nowDate = LocalDate.now();
LocalDateTime nowDateTime = LocalDateTime.now();

varTableTunnel.save(
    new VarTable()
        .setName("JDBC=UTC      - " + ZonedDateTime.now())
        .setTs(nowTs)
        .setDateVal(nowDate)
        .setDatetimeVal(nowDateTime)
        .setTimestampVal(nowDateTime));

mysql root@localhost:p1_db> select name,  FROM_UNIXTIME(ts/1000), timestamp_val, datetime_val from var_table;
+--------------------------------------------------------------+----------------------------+---------------------+---------------------+
| name | FROM_UNIXTIME(ts/1000) | timestamp_val | datetime_val |
+--------------------------------------------------------------+----------------------------+---------------------+---------------------+
| JDBC=Shanghai - 2019-11-26T17:02:46.282+08:00[Asia/Shanghai] | 2019-11-26 17:02:46.278000 | 2019-11-27 01:02:46 | 2019-11-26 17:02:46 |
| JDBC=Shanghai - 2019-11-26T09:02:59.020Z[UTC] | 2019-11-26 17:02:59.018000 | 2019-11-27 01:02:59 | 2019-11-26 17:02:59 |
| JDBC=UTC - 2019-11-26T09:03:22.571Z[UTC] | 2019-11-26 17:03:22.569000 | 2019-11-26 17:03:23 | 2019-11-26 09:03:23 |
| JDBC=UTC - 2019-11-26T17:03:32.992+08:00[Asia/Shanghai] | 2019-11-26 17:03:32.987000 | 2019-11-26 17:03:33 | 2019-11-26 09:03:33 |
+--------------------------------------------------------------+----------------------------+---------------------+---------------------+

-- 查询 MySQL 全局，当前 session，MySQL 所在系统的时区
SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone;

-- 设定 session 时区（当前连接，比如当前控制台所用时区）
SET time_zone='Asia/Shanghai';
SET time_zone='+08:00';
SET time_zone='UTC';

-- 设定 MySQL 服务器全局时区
SET GLOBAL time_zone='Asia/Shanghai';
SET GLOBAL time_zone='+08:00';
SET GLOBAL time_zone='UTC';
下一页

最近更新于 0001-01-01

comments powered by Disqus

© 2017-2022 NGTE all rights reserved