PostgreSQL日期时间

最新推荐文章于 2024-09-23 09:00:00 发布

讓丄帝愛伱

最新推荐文章于 2024-09-23 09:00:00 发布

阅读量948

点赞数 1

分类专栏：数据库文章标签：数据库 oracle sql

本文链接：https://blog.csdn.net/ximaiyao1984/article/details/128103153

版权

数据库专栏收录该内容

125 篇文章 1 订阅

订阅专栏

这篇博客详细介绍了SQL中处理日期和时间的各种函数和表达式，包括获取当前日期、时间戳转换、日期运算以及提取年、月、日、小时等信息。还展示了如何计算过去和将来的日期，以及获取一周内的日期范围。对于数据库开发和管理，这些技巧非常实用。

摘要由CSDN通过智能技术生成

-- select to_char(t.detect_date,'yyyy')
-- 当前日期
SELECT CURRENT_DATE;
select CURRENT_TIMESTAMP :: DATE AS "当前日期";
Select '当前日期：' || to_char(now(),'YYYY-MM-DD') AS "当前日期";
SELECT now();
SELECT CURRENT_TIME, LOCALTIME, CURRENT_TIMESTAMP, LOCALTIMESTAMP;
SELECT DATE 'epoch', DATE 'today',DATE 'tomorrow', DATE 'yesterday', TIME 'allballs';

select extract(year from now()); -- 年
select to_char((SELECT now()::timestamp),'yyyy'); -- 年
select to_char((SELECT now()::timestamp),'yyyyMMdd'); -- 年月日
select to_char(now(),'YYYY-MM-dd');

Select 
	EXTRACT ( YEAR FROM now()) AS "年",
	EXTRACT ( MONTH FROM now()) AS "月",
	EXTRACT ( DAY FROM now()) AS "日",
	EXTRACT ( HOUR FROM now()) AS "时",
	EXTRACT ( MINUTE FROM now()) AS "分",
	EXTRACT ( SECOND FROM now()) AS "秒";

常用

-- 上一年
select to_char((SELECT now()::timestamp+ '1 year'),'yyyy');
-- 下一年
select to_char((SELECT now()::timestamp+ '-1 year'),'yyyy');
-- 四天前
select to_char(( DATE ( now() ) -  INTERVAL '4 day' ) :: DATE,'YYYY-MM-DD');

select
to_char((CURRENT_TIMESTAMP - INTERVAL '1 day'),'YYYY-MM-DD') AS "一天前",
to_char((CURRENT_TIMESTAMP + INTERVAL '1 day'),'YYYY-MM-DD') AS "一天后";
-- 最近七天时间
SELECT DATE( T ) AS DAY
FROM
generate_series ( ( CURRENT_TIMESTAMP - INTERVAL '6 day' ) :: DATE,CURRENT_TIMESTAMP :: DATE, '1 days' ) AS T


select
    now() - interval '10' YEAR as "10年前",
    now() - interval '10' MONTH as "10个月前",
    now() - interval '10' day as "10天前",
    now() - interval '10' hour as "10小时前",
    now() - interval '10' minute as "10分钟前",
    now() - interval '10' second as "10秒钟前",
    now() + interval '10' YEAR as "10年后",
    now() + interval '1-3' year to month AS "一年零三个月后"

-- interval '1-3' ：1为年间隔，3为月间隔

-- 第几周
SELECT date_part('week',TIMESTAMP '2021-03-11');
SELECT date_part('week', CURRENT_DATE);
SELECT date_part('week', now());

-- 周几
select EXTRACT(dow FROM DATE(now())) AS "星期"

--周一
select to_char(( DATE ( now() ) - ( EXTRACT ( dow FROM DATE ( now() ) ) - 1 || ' day' ) :: INTERVAL ) :: DATE,'YYYY-MM-DD');
--周日
select to_char(( DATE ( now() ) - ( EXTRACT ( dow FROM DATE ( now() ) ) - 1 || ' day' ) :: INTERVAL ) :: DATE + 6,'YYYY-MM-DD');
--本周七天
SELECT DATE(T) AS DAY
FROM generate_series((DATE(now()) - (EXTRACT(dow FROM DATE(now())) - 1 || ' day') :: INTERVAL) :: DATE,
                     (DATE(now()) - (EXTRACT(dow FROM DATE(now())) - 1 || ' day') :: INTERVAL) :: DATE + 6,
                     '1 days') AS T;

-- 时间
SELECT date_trunc('week', '2021-03-11'::timestamp);
SELECT date_trunc('week', CURRENT_DATE::timestamp);