mysql 、pg 查询日期处理

原创已于 2024-12-17 09:54:57 修改 · 2.5k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #sql

于 2022-08-08 15:12:04 首次发布

笔记同时被 2 个专栏收录

22 篇文章

订阅专栏

数据库

5 篇文章

订阅专栏

这篇博客详细介绍了如何在MySQL和PostGIS中进行日期和时间的格式化、截取、转换以及范围查询。包括使用date_format、left、right、str_to_date、FROM_UNIXTIME等函数，以及INTERVAL和YEARWEEK等概念。同时，也展示了如何在Mybatis中处理日期时间。对于数据库开发者和数据分析师来说，这些技巧非常实用。

mysql 👇

日期格式化：
'%Y-%m-%d %H:%m:%s' - 完整格式化字符串

-- 年月
select create_time,date_format(create_time,'%Y-%m') from tableName

-- 这截取查询出来是字符
select create_time,left(create_time,7) from tableName

-- 时分秒
select create_time,date_format(create_time,'%H:%m:%s') from tableName

select create_time,right(create_time,8) from tableName

-- 字符转日期
SELECT stat_time, str_to_date(stat_time, '%Y-%m-%d') as statDate FROM tableName

-- 时间戳转日期
date_format(FROM_UNIXTIME(statistical_time/1000,'%Y-%m-%d %H:%i:%S'),'%Y-%m-%d')

日期范围：

-- 查询当天
select DATE(NOW());

-- 查询昨天
select  date_format(DATE(NOW())-1, '%Y-%m-%d')

-- 查询 2小时前的数据
select * from tableName
where create_time > NOW()-INTERVAL '2' HOUR and create_time<=now();

-- 时间差
select TIMESTAMPDIFF(MINUTE,create_time,now())
from tableName

-- 查询本周数据, mysql-默认会把上周末，当作本周
SELECT create_time FROM tableName WHERE YEARWEEK(date_format(create_time,'%Y-%m-%d')) = YEARWEEK(now());

-- 查询本周条件
where date_format(create_time,'%Y-%m-%d')
BETWEEN
DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY)
AND 
DATE_ADD(CURDATE(), INTERVAL 6 - WEEKDAY(CURDATE()) DAY)

-- 查询本月
WHERE DATE_FORMAT(create_time, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m');

-- 上周
SELECT create_time FROM tableName WHERE YEARWEEK(date_format(create_time,'%Y-%m-%d')) = YEARWEEK(now())-1;

-- 今天+往前6天
WHERE date(create_time) BETWEEN CURDATE() - INTERVAL 6 DAY AND CURDATE();

postgis👇

日期范围：

-- 查询 2小时前的数据 
select * from tableName
where  create_time >= now() + ' -2 Hours ' AND create_time <= now()

-- 5分钟范围
SELECT
	 FROM_UNIXTIME( statistical_time / 1000, '%Y-%m-%d %H:%m:%s' )
FROM
	station_population_info 
  where FROM_UNIXTIME( statistical_time / 1000, '%Y-%m-%d %H:%m:%s' ) between date_add(now(), interval - 5 minute) and now()

-- mybatis 中采用静态替换 如：' ${param.intervalTime} ' ，来表示 ' -2 Hours '

日期转换：'yyyy-MM-dd hh24:mi:ss' --完整格式

-- 时间戳转时间
select position_time,to_timestamp(position_time/1000) from tableName

-- 日期转字符
select position_time,to_char(to_timestamp(position_time/1000), 'yyyy-mm-dd') from tableName

-- 日期、时间分开存储时，日期转换
select date_str,time_str,to_timestamp( concat ( date_str, ' ', time_str ), 'yyyy-MM-dd hh24:mi:ss' ) from tableName