PostgreSQL日期时间

-- 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);