6.数据类型-时间/日期类型

---

前言

一、时间/日期类型

3.1.时间/日期类型列表

字符类型名称	存储长度	描述
timestamp[§] [without time zone]	8字节	包括日期和时间，不带时区，简写成timestamp
timestamp[§] with time zone	8字节	包括日期和时间，带时区
date	4字节	日期，但不包含一天中的时间
time[§][without time zone]	8字节	一天中的时间，不包含日期，不带时区
time[§] with time zone	12字节	一天中的时间，不包含日期，带时区
interval [fields][§]	16字节	时间间隔

now()函数显示当前时间，返回类型为timestamp[(p)] with time zone:
select now();
lhcdb@10.206.132.17:5432=>select now();
              now
-------------------------------
 2021-10-20 11:43:41.282824+08
(1 row)


类型转换:不带时区
select now()::timestamp without time zone;
注意:SQL中的两个冒号是指类型转换。
lhcdb@10.206.132.17:5432=>select now()::timestamp without time zone;
            now
----------------------------
 2021-10-20 11:44:09.725417
(1 row)

            
转换成date格式:
select now()::date;

转换成time without time zone:
select now()::time without time zone;
lhcdb@10.206.132.17:5432=#select now()::date;
    now
------------
 2021-10-20
(1 row)


转换成time with time zone:
select now()::time with time zone;
lhcdb@10.206.132.17:5432=#select now()::time with time zone;
        now
--------------------
 13:48:13.429742+08
(1 row)


interval是指时间间隔，时间间隔单位可以是hour、day、month、year等
select now(),now()+interval '1 day';
lhcdb@10.206.132.17:5432=#select now(),now()+interval '1 day';
              now              |           ?column?
-------------------------------+-------------------------------
 2021-10-20 13:48:54.215032+08 | 2021-10-21 13:48:54.215032+08
(1 row)

select now(),now()::timestamp(0);
lhcdb@10.206.132.17:5432=#select now(),now()::timestamp(0);
              now              |         now
-------------------------------+---------------------
 2021-10-20 13:49:16.106678+08 | 2021-10-20 13:49:16
(1 row)

注意:时间类型中的§是指时间精度，具体指秒后面小数点保留的位数，如果没有声明精度默认值为6.

3.2.时间/日期类型操作符

日期相加
select date '2021-10-09' + interval '1 days';
lhcdb@10.206.132.17:5432=#select date '2021-10-09' + interval '1 days';
      ?column?
---------------------
 2021-10-10 00:00:00
(1 row)


日期相减
select date '2021-10-09' - interval '1 hour';
lhcdb@10.206.132.17:5432=#select date '2021-10-09' - interval '1 hour';
      ?column?
---------------------
 2021-10-08 23:00:00
(1 row)


日期相乘
select 100 * interval '1 second';
lhcdb@10.206.132.17:5432=#select 100 * interval '1 second';
 ?column?
----------
 00:01:40
(1 row)

日期相除
select interval '1 hour' / double precision '3';
lhcdb@10.206.132.17:5432=#select interval '1 hour' / double precision '3';
 ?column?
----------
 00:20:00
(1 row)

3.3.时间/日期类型常用函数

显示当前的时间
select current_date,current_time;
lhcdb@10.206.132.17:5432=#select current_date,current_time;
 current_date |    current_time
--------------+--------------------
 2021-10-20   | 13:53:14.589572+08
(1 row)


extract函数,可以从日期、时间数据类型中抽取年、月、日、时、分、秒信息:

extract(field from source)
field值可以为century、year、month、day、hour、minute、second等
source类型为timestamp、time、interval的值的表达式

取年份
select extract(year from now());
lhcdb@10.206.132.17:5432=#select extract(year from now());
 date_part
-----------
      2021
(1 row)

对于timestamp类型,取月份和月份里的第几天:
select extract(month from now()),extract(day from now());
lhcdb@10.206.132.17:5432=#select extract(month from now()),extract(day from now());
 date_part | date_part
-----------+-----------
        10 |        20
(1 row)

取小时、分钟:
select extract(hour from now()),extract(minute from now());
lhcdb@10.206.132.17:5432=#select extract(hour from now()),extract(minute from now());
 date_part | date_part
-----------+-----------
        13 |        56
(1 row)

取秒:
select extract(second from now());
lhcdb@10.206.132.17:5432=#select extract(second from now());
 date_part
-----------
 19.761273
(1 row)

取当前日期所在年份中的第几周:
select extract(week from now());
lhcdb@10.206.132.17:5432=#select extract(week from now());
 date_part
-----------
        42
(1 row)

当天属于当年的第几天:
select extract(doy from now());
lhcdb@10.206.132.17:5432=#select extract(doy from now());
 date_part
-----------
       293
(1 row)