mysql 、pg 查询日期处理

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

-- 查询本周数据
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;

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