Postgresql学习笔记之——数据类型之日期类型

一、日期和时间的类型如下
名称存储空间描述例子
timestamp [ p ] [ without time zone ]8字节显示日期加时间2020-02-23 12:00:00
timestamp [ p ] with time zone8字节显示日期、时间加时区
interval [ § ]12字节时间间隔
date4字节只用于日期
time [ p ] [ without time zone ]8字节只用于1日内时间,不带时区
time [ p ] with time zone8字节只用于1日内时间,带有时区
二、日期输入

在SQL中,任何日期或时间的文本输入需要有 ’ 日期/时间 ‘ 类型加单引号包围的字符串组成,语法:
type [ ( p ) ] ’ value ’
日期和时间的输入几乎可以是任何合理的形式,在Postgresql中有系统参数 datestyle 决定是什么格式或形式,如下:

postgres=# show datestyle;
 DateStyle 
-----------
 ISO, MDY
(1 row)

其中 “ MDY ” 表示 月-日-年
在数据库中参数可以设置成 YMD 的形式,但在使用语句插入时,用了date转换格式输入,参数设置那种格式没有影响,都可以进行插入成功。

postgres=# show datestyle;
 DateStyle 
-----------
 ISO, MDY
(1 row)

postgres=# insert into tb_date values(date '02-23-2020');
INSERT 0 1
postgres=# insert into tb_date values(date '2020-02-23');
INSERT 0 1
postgres=# select * from tb_date ;
    col1    
------------
 2020-02-23
 2020-02-23
(2 rows)

但是有时候日期输入习惯使用诸如此类 ’ 1/8/2020 ‘ 的格式,类似这种各位月或日没有0做前缀时要注意参数 datestyle 的值,防止日期混乱或者插入错误。
日期输入示例:

例子描述
date ‘April 26,2020’在任何datestyle参数值下都没有输入问题
date ‘2019-01-08’ISO 8601格式,任何方式输入没有问题
date ‘1/8/2019’在datestyle为 MDY 时,表示2019年1月8日,在datestyle为 DMY 时,表示2019年8月1日,类似输入日期文本时没有在一位数月份或日前加前缀0的输入方式一定要注意 datestyle 参数的格式,凡是日期混乱
date ‘1/18/2020’在datestyle参数值为 MDY 时,表示2020年1月18日,其他格式会出现输入错误
date ‘03/04/11’在 MDY 格式下表示2011年3月4日,在DMY 格式下表示2011年4月3日,在YMD格式下表示2003年4月11日,所以建议在输入日期文本时要输入完整的日期文本,尽量不要用简写导致日期混乱。
date ‘2019-Apr-08’在任何格式下都是2019年4月8日
date ‘Apr-08-2019’在任何格式下都是2019年4月8日
date ‘08-Apr-2019’在任何格式下都是2019年4月8日
date ‘20190405’ISO 8601格式,任何格式下都是2019年4月5日
date ‘110405’ISO 8601 格式,在任何格式下都是2011年4月5日
date ‘2019.098’2019年的第98天,即2019年4月8日
date ‘J2455886’儒略日,即从公元前4713年1月1日期到今天经过的天数,多为天文学家使用,2455886天,就是2011年11月20日
date ‘April 26,202 BC’公元前202年4月26日

PS: 对于国内的程序员来说,避免使用 ’/’ 进行日期的输入,,最好使用 ‘-’ 来进行日期文本分割,然后使用 YMD 也就是 年-月-日
的格式输入日期。

三、时间输入

时间输入时,要注意时区的输入,time 被认为时 time without time zone 的类型,所以即使输入的字符串中有时区,也会被忽略,例如:

postgres=# select time '12:59:59';
   time   
----------
 12:59:59
(1 row)

postgres=# select time '12:59:59 PST';
   time   
----------
 12:59:59
(1 row)

postgres=# select time with time zone  '12:59:59 PST';
   timetz    
-------------
 12:59:59-08
(1 row)

时间输入时字符串之间使用冒号作为分隔符,输入格式为: ‘ hh:mm:ss ’,如:‘ 12:59:59 ’,也可以不使用分隔符,如:‘125959’ 表示 12时59分59秒。
更多的时间输入示例如下:

例子描述
time ‘12:59:59.789’ISO 8601
time ‘12:59:59’ISO 8601
time ‘12:59’ISO 8601
time ‘125959’ISO 8601
time ‘10:59 AM’与 10:59 一样,AM不影响数值
time ‘10:59 PM’与 22:59一样,使用PM时输入的小时的值小于等于12
time with time zone ‘12:59:59.789+8’带有时区
time with time zone ‘12:59:59+08:00’ISO 8601
time with time zone ‘12:59+08:00’ISO 8601
time with time zone ‘125959+08:00’ISO 8601
time with time zone ‘12:59:59 CCT’带有缩写的时区,北京时间12:59:59
select time with time zone ‘2020-02-24 12:59:59 Asia/Beijing’用具体名字声明时区

PS: 建议在输入时区时不适用缩写表示,相同的缩写代表的可能时不同的时区。

四、特殊的时间字符串

在Postgresql中有一些特殊的字符串输入表示了特别的意义,如下:

字符串使用类型描述
epochdate,timestamp1970-01-01 00:00:00+00(Unix 系统的零时)
infinitytimestamp时间戳的最大值,比任何其他时间戳都晚
-infinitytimestamp时间戳的最小值,比任何其他时间戳都早
nowdate,time,timestamp当前事务的开始时间(当前时间)
todaydate,timestamp今日午夜
tomorrowdate,timstamp明日午夜
yesterdaydate,timestamp昨日午夜
allballstime00:00:00.00 UTC
五、时间函数和操作符

日期、时间和interval类型之间可以进行加减乘除运算,具体如下:

操作符:

操作符例子结果
+date ‘2020-02-01’ + integer ‘7’date ‘2020-02-08’
+date ‘2020-02-01’ + interval ‘1 hour’timestamp ‘2020-0201 01:00:00’
+date ‘2020-02-01’ + time ‘03:00’timestamp ‘2020-02-01 03:00:00’
+interval ‘1 day’ + interval ‘1 hour’interval ‘1 day 01:00:00’
+timestamp ‘2020-02-01 01:00’ + interval ‘23 hours’timestamp ‘2020-02-02 00:00:00’
+time ‘01:00’ + interval ‘2 hours’time ‘03:00:00’
-date ‘2020-02-23’ - date ‘2020-02-20’3
-date ‘2020-02-23’ - integer ‘3’date ‘2020-02-20’
-date ‘2020-02-23’ - interval ‘1 hour’timestamp ‘2020-02-22 23:00:00’
-time ‘07:00’ - time ‘03:00’interval ‘04:00:00’
-time ‘07:00’ - interval ‘4 hours’time ‘03:00:00’
-timestamp ‘2020-02-23 22:00’ - interval ‘22 hour’timestamp ‘2020-02-23 00:00:00’
-interval ‘a day’ - interval ‘1 hour’interval ‘1 day -01:00:00’
-timestamp ‘2020-02-23 03:00:00’ - timestamp ‘2020-02-20 12:00’interval ‘2 days 15:00:00’
*600*interval ‘1 second’interval ‘00:01:00’
*15*interval ‘1 day’interval ‘15 days’
*2.5*interval ‘1 hour’interval ‘02:30:00’
/interval ‘2 hour’ / 1.5interval ‘01:22:00’

函数如下:

函数返回类型描述例子结果
age(timestamp,timestamp)interval两个时间段之间的时间间隔,前者时间一般为较新时间,结果为正数的时间间隔,否则为负数的时间间隔age(timestamp ‘2020-02-01’,timestamp ‘2019-01-01’); age(timestamp ‘2019-02-01’,timestamp ‘2020-01-01’);1 year 1 mon ;-11 mons
age(timestamp)interval从当前时间减去参数时间的结果(时间间隔)select age(timestamp ‘2000-01-01’)20 years 1 mon 23 days
clock_timestamp()timestamp with time zone实时时钟的当前时间戳,带有时区select clock_timestamp();
current_datedate当前日期,注意使用时不用加括号select current_date
current_timetime with time zone当前时间,带有时区select current_time;
current_timestamptimestamp with time zone当前事务开始时的时间戳,带有时区select current_timestamp
date_part(text, timestamp)double precision获取时间timestamp中指定年月日时分秒等,text的值包括了year(简写 y ),month(m),day(d),hour(h),minute(m),second(s)select date_part(‘day’,timestamp ‘2020-02-23 12:59:30’);23
date_part(text, interval)double precision获取interval时间类型中的指定的年月日时分秒等,text的值包括了year(简写 y ),month(m),day(d),hour(h),minute(m),second(s)select date_part(‘hour’,interval ‘2 years 3 months 12 days 10 hours 30 minutes 10 seconds’);10
date_trunc(text, timestamp)timestamp将时间timestamp截断成text指定的精度,text的值包括了year(简写 y ),month(m),day(d),hour(h),minute(m),second(s)select date_trunc(‘hour’,timestamp’2020-02-23 12:59:30’);2020-02-23 12:00:00
extract(field from timstamp)double precision获取timestamp中指定的text,类似date_partselect extract(‘hour’ from timestamp ‘2020-02-23 12:59:30’);12
extract(field from interval)double pracision获取interval中指定的text,类似date_partselect extract(‘hour’ from interval ‘2 days 12 hours’)12
isfinite(timestamp)boolean测试是否为有穷时间戳isfinite(timestamp ‘2020-02-25 20:12:30’)true
isfinite(interval)boolean测试是否为有穷时间隔isfinite(interval ‘5 hours’)true
justify_days(interval)interval按照每天30天调整时间间隔,每个30天会变为一个月,不够显示真是天数,超出显示减去30天后的天数select justify_days(interval ‘35 days’);1 mon 5 days
justify_hours(interval)interval按照每24小时调整为1天,满24小时会以day显示,如果输入的是day,只会显示dayselect justify_hours(interval ‘35 hours’);1 day 11:00:00
justify_interval(interval)interval同时具备justify_days与justify_hours的功能,而interval参数可以进行加减计算select justify_interval(interval ‘1 mon - 2 days’);28 days
localtimetime当日时间select localtime;20:39:09.384134
localtimestamptimestamp当前事务开始时的时间戳select localtimestamp;2020-02-25 20:40:17.168613
now()timestamp with time zone当前事务开始时的时间戳并带有时区select now();2020-02-25 20:41:28.969894+08
statement_timestamp()timestamp with time zone与now()类似,实时时钟的当前时间戳select statement_timestamp();2020-02-25 20:42:24.416925+08
timeofday()text与statment_timestamp类似,但是返回的类型是text字符串,而非时间类型,显示的格式也不一样select timeofday();Tue Feb 25 20:44:17.330426 2020 CST
transaction_timestamp()timestamp with time zone当前事务开始时的时间戳select transaction_timestamp()2020-02-25 20:46:02.107255+08

除了以上的函数外,Postgresql还支持SQL的OVERLAPS操作符,如:
(start1,end1)OVERLAPS(start2,end2)
(start1,length1)OVERLAPS(start2,length2)
以上两个表达式表示在左边的时间段与右边的时间段是否有重叠,如果重叠返回true,如果没有重叠返回false。终点参数(end1、end2)可以是日期,时间,时间戳。或者后面(length1、length2)是一个时间间隔的日期、时间、时间戳。如:

postgres=# select (date '2019-01-01',date '2019-09-25')overlaps(date '2019-09-01',date '2020-02-25');
 overlaps 
----------
 t
(1 row)

postgres=# select (date '2019-01-01',date '2019-08-31')overlaps(date '2019-09-01',interval '25 days');
 overlaps 
----------
 f
(1 row)
六、时间函数

Postgresql数据库中内置了很多返回当前日期和时间的函数,一下函数是按照当前事务的开始时间返回结果:
current_date
current_time
current_timestamp
current_time(precision)
current_timestamp(precision’)
localtime
localstamp
localtime(precision)
localtimestamp(precision)
now()
transacion_timestamp()
其中precision代表的是精度,该精度会导致结果的秒数域会四舍五入到指定的小数位,如果没有精度参数,将给予全部得到的精度。

PS: 特别介绍一下,当前时间获取的函数分为事务开始时的时间戳和实时时钟(当前时间)的时间两类,分别如下:
1.当前事务开始时的时间戳。开始事务时也就是begin;执行时表示开始了一个事务,而当前事务开始时的当前时间,表示执行begin时的时间,在end结束事务前,获取的当前时间时不变的,都是相同一个时间。主要函数包括:current_date、current_time、current_timestamp、localtime、localstamp、transaction_timestamp().。Postgresql这么做的目的是为了允许一个事务在当前时间上有连贯的概念,同一个事务里的多个修改可以保持同样的时间戳。
2.实时时钟时间表示即便执行begin开始了事务,获取的当前时间仍是随着时间而改变的,类似直接获取的是系统时间,函数有:clock_timestamp()、statement_timestamp()、stimeofday()。

所有的日期或时间还接受特殊的文本值:now。用于声明当前的时间和日期(注意:是当前事务开始的开始时间),所以,一下三个语句返回结果是相同的:

postgres=# begin;
BEGIN
postgres=# select current_timestamp;
       current_timestamp       
-------------------------------
 2020-02-25 21:24:32.964255+08
(1 row)

postgres=# select now();
              now              
-------------------------------
 2020-02-25 21:24:32.964255+08
(1 row)

postgres=# select timestamp with time zone 'now';
          timestamptz          
-------------------------------
 2020-02-25 21:24:32.964255+08
(1 row)

postgres=# end;
COMMIT

七、extract函数详解
extract函数格式:
extract(field from source)
extract函数从日期或时间数值中抽取子域,比如年分、小时等,返回类型为 double precision 的数值,source参数必须是一个timestamp、time、interval类型的值的表达式,此外,类型是date的表达式自动转换为timestamp,所以source也可用date类型,field参数是一个标识符或者字符串,指定了从源数据中抽取的域,下表列举了field可以取得值:

field值说明例子结果
century世纪select extract(century from timestamp ‘2020-02-25 12:59:59’);21
year年份select extract(year from timestamp ‘2020-02-25 12:59:59’);2020
decade得到年份除以10后的值select extract(decade from timestamp ‘2020-02-25 12:59:59’);202
millennium得到当前是第几个千年(0-1000是第一个,1001-2000是第二个,2001-3000是第三个)select extract(millennium from timestamp ‘2020-02-25 12:59:59’);3
quarter是第几季度select extract(quarter from timestamp ‘2020-02-25 12:59:59’);1
month当source是timestamp得到的是月份,当source是interval得到是月的数目select extract(month from timestamp ‘2020-02-25 12:59:59’); l select extract(month from interval ‘2 years 8 months’);2 l 8
week得到所给的日期是这一年的第几个星期select extract(week from timestamp ‘2020-02-25 12:59:59’);9
dow得到所给的日期是星期几,0是星期天,1是星期一 。。。select extract(dow from timestamp ‘2020-02-25 12:59:59’);2
day本月的第几天select extract(day from timestamp ‘2020-02-25 12:59:59’);25
doy本年的第几天select extract(doy from timestamp ‘2020-02-25 12:59:59’);56
hour得到时间中的小时(0-23)select extract(hour from timestamp ‘2020-02-25 12:59:59’);12
minute得到时间中的分钟select extract(minute from timestamp ‘2020-02-25 12:59:59’);59
second得到时间中的秒,包括小数部分select extract(second from timestamp ‘2020-02-25 12:59:59.7521’);59.7521
epoch对于date和timestamp值来说,得到的是自1970-01-01 00:00:00以来的秒数;对于interval值来说,得到时间间隔的总秒数select extract(epoch from timestamp ‘2020-02-25 12:59:59.7521’); l select extract(epoch from interval ‘2 days 12 hours’);1582635599.7521 l 216000
milliseconds秒域(包括小数)乘以 1000,即秒域的毫秒级select extract(milliseconds from timestamp ‘2020-02-25 12:59:59.7521’);59752.1
microseconds秒域(包括小数部分)乘以 1000000,即秒域的微秒级select extract(microseconds from timestamp ‘2020-02-25 12:59:59.7521’);59752100
timezone与UTC的时区偏移量,以秒记录。例如中国是 +8区,返回的是3600x8=28800select extract(timezone from timestamp with time zone ‘2020-02-25 12:59:59.7521’);28800
timezone_hour时区偏移量的小时部分select extract(timezone_hour from timestamp with time zone ‘2020-02-25 12:59:59.7521’);8
timezone_minute时区偏移量分钟部分,整数时区返回0select extract(timezone_minute from timestamp with time zone ‘2020-02-25 12:59:59.7521’);0
  • 5
    点赞
  • 23
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
好的,我可以和你分享一些关于 PostgreSQL学习笔记。 1. PostgreSQL 是什么? PostgreSQL 是一种开源的关系数据库管理系统,它支持 SQL 语言和 ACID 事务处理,并且具有丰富的特性和扩展性,可用于大企业应用、Web 应用和嵌入式应用。 2. PostgreSQL 的特点 PostgreSQL 具有以下特点: - 开源免费 - 支持 ACID 事务处理 - 支持 SQL 语言 - 支持复杂查询和数据类型 - 支持存储过程和触发器 - 可以扩展和定制 - 可以跨平台运行 3. PostgreSQL 的安装 在 Windows 平台下,可以从官网下载 Windows 安装程序进行安装。在 Linux 平台下,可以使用包管理器安装,例如 Ubuntu 可以使用以下命令进行安装: ``` sudo apt-get install postgresql ``` 4. PostgreSQL 的基本操作 创建数据库: ``` CREATE DATABASE databasename; ``` 创建表: ``` CREATE TABLE tablename ( column1 datatype, column2 datatype, column3 datatype, .... ); ``` 插入数据: ``` INSERT INTO tablename (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); ``` 查询数据: ``` SELECT column1, column2, ... FROM tablename WHERE condition; ``` 更新数据: ``` UPDATE tablename SET column1 = value1, column2 = value2, ... WHERE condition; ``` 删除数据: ``` DELETE FROM tablename WHERE condition; ``` 5. PostgreSQL 的扩展 PostgreSQL 可以通过扩展来增强其功能,例如: - PostGIS:空间数据处理扩展 - PL/Python:Python 语言扩展 - pgAdmin:可视化管理工具 - pgpool-II:连接池和负载均衡工具 以上就是一些关于 PostgreSQL 的基本学习笔记,希望对你有所帮助。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Major_ZYH

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值