postgresql查询今天、昨天、本周、本月、上月、今年、去年的时间以及计算时间之差

时间：2024-09-22 15:16:41|栏目：PostgreSQL|点击：次

前言

在项目遇到一个需求是查询统计今天、昨天、本周、本月、上月、今年、去年的时间数据，最近一个月的，最近一年的月份数据，使用的是pgSql数据库：

获取当前系统时间

ｓｅｌｅｃｔ now();
ｓｅｌｅｃｔ current_timestamp;
结果：2020-11-04 16:09:53.247825+08

获取当前日期或时间

ｓｅｌｅｃｔ current_date;
结果：2020-11-04

ｓｅｌｅｃｔ current_time;
结果：16:14:08.501182+08

查询昨天的数据

ｓｅｌｅｃｔ
    DISTINCT count(id)
from
    表名
where coalesce(l.join_date,l.sys_createdate) >= current_date - 1;
 
# 这里的coalesce函数，语法：coalesce(expr1,expr2,expr3...)
# 如果第一个字段存在就用第一个进行表达式判断；
# 如果第一个不存在为null则用第二个进行表达式判断；
# 如果都没有null则返回null

查询某个时间的周一

ｓｅｌｅｃｔ ( DATE '2020-10-23' - INTERVAL '1 day' - ( EXTRACT ( dow FROM ( DATE'2020-10-23' - INTERVAL '1 day' ) ) - 1 || ' day' ) :: INTERVAL ) :: DATE;
# 减1 是因为得到的是以周一是星期的开始

查询本周的数据

ｓｅｌｅｃｔ( DATE ( now() ) - ( EXTRACT ( dow FROM DATE ( now() ) ) - 1 || ' day' ) :: INTERVAL ) :: DATE startasy,
( DATE ( now() ) - ( EXTRACT ( dow FROM DATE ( now() ) ) - 1 || ' day' ) :: INTERVAL ) :: DATE + 6 endday
    from 表名 LIMIT 1;

本周最后一天

ｓｅｌｅｃｔ to_char(CURRENT_DATE +cast(-1*(TO_NUMBER(to_char(CURRENT_DATE,'D'),'99')-2) + 6 ||' days' as interval),'yyyy-mm-dd');
# 加6天就表示是周日 不加就是本周的第一天

本月,方式一

1	`ｓｅｌｅｃｔ` `to_char((ｓｅｌｅｃｔ` `now()` `as` `timestamp),'mm');`

本月,方式二

1	`ｓｅｌｅｃｔ` `*` `from` `表名` `where` `time` `>= date_trunc(` `'month', now() );`

获取上月

ｓｅｌｅｃｔ to_char((ｓｅｌｅｃｔ  now() - interval '1 month'),'mm');

获取今年

1	`ｓｅｌｅｃｔ` `to_char((ｓｅｌｅｃｔ` `now()` `as` `timestamp),'yyyy')`

获取去年

ｓｅｌｅｃｔ to_char((ｓｅｌｅｃｔ  now() - interval '1 years'),'yyyy')

（补充）获取过去12个月或者今年月份的数据

WITH RECURSIVE T (n) AS (
ｓｅｌｅｃｔ DATE(to_char( to_date('2022-08', 'yyyy-MM-dd') - INTERVAL '11 month', 'yyyy-MM-dd' ))
UNION ALL
ｓｅｌｅｃｔ
n + 1
FROM
T
WHERE
n < DATE( to_char( to_date('2022-08', 'yyyy-MM-dd'), 'yyyy-MM-dd' ) )
) ｓｅｌｅｃｔ
to_char( n, 'yyyy-MM' ) AS MONTH
FROM T
GROUP BY
MONTH ORDER BY Month
# 只要月份修改成2022-12就表示查询今年内的所有月份

（补充）获取过去一个月内的所有天的数据

WITH RECURSIVE T ( n ) AS (
ｓｅｌｅｃｔ DATE
( to_char( now( ) - INTERVAL '30 day', 'yyyy-MM-dd' ) ) UNION ALL
ｓｅｌｅｃｔ
n + 1
FROM
T
WHERE
n < DATE ( to_char( now( ), 'yyyy-MM-dd' ) )
) ｓｅｌｅｃｔ
to_char( n, 'yyyy-MM-dd' ) AS DAYS
FROM T
GROUP BY
DAYS
ORDER BY DAYS

（补充）获取上个月每天的数据（1号-31号）

ｓｅｌｅｃｔ
 generate_series (
  date_trunc( 'month', CURRENT_DATE - interval '1 month'),
  date_trunc( 'month', CURRENT_DATE) - interval '1 day',
  '1 d' :: INTERVAL
 ) :: DATE days

（补充）获取本月每天的数据（1号-31号）

ｓｅｌｅｃｔ
 generate_series (
  date_trunc( 'month', CURRENT_DATE),
  date_trunc( 'month', CURRENT_DATE)  + '1 month -1d',
  '1 d' :: INTERVAL
 ) :: DATE days

（补充）获取某一天24小时的时刻

ｓｅｌｅｃｔ to_char(t,'yyyy-MM-DD HH24') as day
from
generate_series('2022-01-01 00:00:00'::DATE,'2022-01-01 23:00:00', '1 hours') as t order by day asc;

或者这样写(方便传参)：获取某一天24小时的时刻

ｓｅｌｅｃｔ
    to_char( T, 'yyyy-mm-dd HH24' ) AS HOUR
FROM
    generate_series ( to_date( '2022-01-01', 'yyyy-MM-dd HH24:mi:ss' ), to_timestamp( ｃｏｎｃａｔ ( '2022-01-01', ' 23:00:00' ), 'yyyy-mm-dd HH24:mi:ss' ), '1 hours' ) AS T 
ORDER BY
HOUR ASC;

同理，可获取某个月的所有天数据

ｓｅｌｅｃｔ
        to_char( T, 'yyyy-mm-dd' ) AS day
FROM
        generate_series ( to_date('2022-11', 'yyyy-MM'), (date_trunc('month', to_date('2022-11', 'yyyy-MM')) + interval '1 month - 1 day')::date, '1 days' ) AS T

同理，可获取某年的所有月份数据（上面写过相似的方法）

ｓｅｌｅｃｔ
    to_char( T, 'yyyy-MM' ) AS MONTH
FROM
    generate_series ( to_date( ｃｏｎｃａｔ ( '2022', '-01-01' ), 'yyyy-MM-dd' ), to_date( ｃｏｎｃａｔ ( '2022', '-12-31' ), 'yyyy-MM-dd' ), '1 month' ) AS T 
ORDER BY
MONTH ASC

获取时间之间的秒差

ｓｅｌｅｃｔ round(date_part('epoch', TIMESTAMP '2022-08-15 17:00:10' - TIMESTAMP '2022-08-15 17:00:00'));
结果：10

获取时间之间的分钟差

ｓｅｌｅｃｔ round(date_part('epoch', TIMESTAMP '2022-08-15 17:10:10' - TIMESTAMP '2022-08-15 17:00:00')/60);
结果：10

获取时间之间的小时差

ｓｅｌｅｃｔ round(date_part('epoch', TIMESTAMP '2022-08-15 19:10:10' - TIMESTAMP '2022-08-15 17:00:00')/60/60);
结果：2

上一篇：navicat连接postgresql、人大金仓等数据库报错解决办法

栏目：PostgreSQL

下一篇：使用python-slim镜像遇到无法使用PostgreSQL的问题及解决方法

本文标题：postgresql查询今天、昨天、本周、本月、上月、今年、去年的时间以及计算时间之差

本文地址：https://www.fushidao.cc/shujuku/800.html

更多PostgreSQL

PostgreSQL

postgresql查询今天、昨天、本周、本月、上月、今年、去年的时间以及计算时间之差

前言

获取当前系统时间

获取当前日期或时间

查询昨天的数据

查询某个时间的周一

查询本周的数据

本周最后一天

本月,方式一

本月,方式二

获取上月

获取今年

获取去年

（补充）获取过去12个月或者今年月份的数据

（补充）获取过去一个月内的所有天的数据

（补充）获取上个月每天的数据（1号-31号）

（补充）获取本月每天的数据（1号-31号）

（补充）获取某一天24小时的时刻

或者这样写(方便传参)：获取某一天24小时的时刻

同理，可获取某个月的所有天数据

同理，可获取某年的所有月份数据（上面写过相似的方法）

获取时间之间的秒差

获取时间之间的分钟差

获取时间之间的小时差

您可能感兴趣的文章

阅读排行

推荐教程