mysql 、pg 查询日期处理

mysql 👇

---

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

-- 年月
select create_time,date_format(create_time,'%Y-%d') 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
1
2
3
4
5
6
7
8
9
10
11
12
13

日期范围：

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

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

-- 查询本周数据
SELECT create_time FROM tableName WHERE YEARWEEK(date_format(create_time,'%Y-%m-%d')) = YEARWEEK(now());
-- 上周
SELECT create_time FROM tableName WHERE YEARWEEK(date_format(create_time,'%Y-%m-%d')) = YEARWEEK(now())-1;
1
2
3
4
5
6
7
8
9
10
11
12

postgis👇

---

日期范围：

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

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

日期转换：'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
1
2
3
4
5
6
7
8