PostgreSQL教程(六):函数和操作符详解

来源:未知作者:数据库 日期:2019/12/25 17:10 浏览:

六、格局相配:

后天来敏而好学一下postgresql涉及时间的字段类型和有个别年华的改换函数

PostgreSQL中提供了三种达成方式相配的方法:SQL LIKE操作符,更近一些的SIMILAHaval TO操作符,和POSIX-风格正则表明式。 1. LIKE:复制代码 代码如下: string LIKE pattern [ ESCAPE escape-character ] string NOT LIKE pattern [ ESCAPE escape-character ] 种种pattern定义多个字串的联谊。假使该string包涵在pattern代表的字串集合里,那么LIKE表明式重临真。和咱们想像的同样,要是LIKE重回真,那么NOT LIKE表明式再次来到假,反之亦然。在pattern里的下划线(_卡塔尔国代表相称任何单个字符,而三个百分号(%卡塔尔国相称任何零或更加多字符,如:复制代码 代码如下: 'abc' LIKE 'abc' true 'abc' LIKE 'a%' true 'abc' LIKE '_b_' true 'abc' LIKE 'c' false 要合营文本的下划线也许百分号,实际不是极其其余字符,在pattern里相应的字符务必前导转义字符。缺省的转义字符是反斜杠,不过你能够用ESCAPE子句钦命三个。要合营转义字符本人,写多少个转义字符。大家也能够通过写成ESCAPE ''的不二等秘书技可行地关闭转义机制,这时,我们就不可能关闭下划线和百分号的生面别开含义了。 关键字ILIKE能够用来替换LIKE,令该相称就最近的区域安装是高低写非亲非故的。那些天性不是SQL标准,是PostgreSQL的恢宏。操作符等效于LIKE, 而*对应ILIKE。还有!和!*操作符分别代表NOT LIKE和NOT ILIKE。全体那一个操作符都以PostgreSQL特有的。

生龙活虎、时间档次:
名字                                  存款和储蓄空间    描述                     最低值          最高值            分辨率
timestamp [ (p) ] [without time zone] 8字节      包罗日期和时间           4713 BC          5874897 AD        1 微秒 / 14 位
timestamp [ (p) ] with time zone      8字节      日期和岁月,带时区       4713 BC          5874897 AD        1 微秒 / 14 位
interval [ (p) ]                      12字节      时间间距                -178000000 年    178000000 年      1 飞秒 / 14 位
date                                  4字节      只用于日期               4713 BC          32767 AD          1 天
time [ (p) ] [ without time zone ]  8字节      只用于26日内时间         00:00:00        24:00:00          1 纳秒 / 14 位
time [ (p) ] with time zone 12字节      只用于15日内时间,带时区 00:00:00+1359    24:00:00-135     1 阿秒 / 14 位

  1. SIMILAEvoque TO正则表明式: SIMILAR TO依照情势是或不是合营给定的字符串而回到真恐怕假。复制代码 代码如下: string SIMILAHighlander TO pattern [ESCAPE escape-character] string NOT SIMILAR TO pattern [ESCAPE escape-character] 它和LIKE特别临近,支持LIKE的通配符('_'和'%'卡塔尔且保持其本意。除了那些之外,SIMILAR TO还援助部分温馨独有的元字符,如: 1卡塔尔(قطر‎. | 标记选拔(多个候选之大器晚成卡塔尔(قطر‎。 2卡塔尔国. * 表示重复前边的项零次或更频仍。 3卡塔尔(英语:State of Qatar). + 表示重复后面包车型客车项三次或更频仍。 4卡塔尔(قطر‎. 可以应用圆括弧(卡塔尔(قطر‎把项组合成叁个逻辑项。 5卡塔尔国. 一个方括弧表明式[...]扬言三个字符表,仿佛POSIX正则表明式同样。 见如下示例:复制代码 代码如下: 'abc' SIMILAHaval TO 'abc' true 'abc' SIMILAR TO 'a' false 'abc' SIMILA宝马7系 TO '%(b|d卡塔尔(قطر‎%' true 'abc' SIMILA逍客 TO '(b|c卡塔尔国%' false 带四个参数的substring,substring(string from pattern for escape-character卡塔尔(قطر‎,提供了一个从字串中抽取贰个万分SQL正则表明式情势的子字串的函数。和SIMILAR TO相像,证明的形式必需合作整个数据串,不然函数失效并回到NULL。为了标记在成功的时候理应回到的格局部分,形式必得现身后跟双引号("卡塔尔(英语:State of Qatar)的多个转义字符。相配那五个标识之间的形式的字串将被再次来到,如: MyTest=# SELECT substring('foobar' from '%#"o_b#"%' FOR '#'); --这里#是转义符,双引号内的格局是回去部分。复制代码 代码如下: substring ----------- oob (1 row卡塔尔(英语:State of Qatar) MyTest=# SELECT substring('foobar' from '#"o_b#"%' FOR '#'卡塔尔国; --foobar不可能完全同盟前边的方式,由此回到NULL。 substring -----------

说明:
1、time ,timestamp 和interval 接纳二个可选的精度值 p,这么些精度值注明在秒域前边小数点之后保留的位数。 缺省的时候在精度上是平昔不通晓的绑定的, p 有用的限制对 timestamp和 interval 是从 0 到轮廓 6
2、对于date和timestamp 数值来讲, 是自壹玖陆柒-01-01 00:00:00来讲的秒数(结果大概是负数);对于interval数值来说,它是光阴间隔的总秒数。

(1 row)

二、当前时间:
test=# select now(),current_timestamp,current_date,current_time;
              now              |              now              |    date    |       timetz      
-------------------------------+-------------------------------+------------+--------------------
2016-01-11 17:22:25.263468+08 | 2016-01-11 17:22:25.263468+08 | 2016-01-11 | 17:22:25.263468+08
(1 row)

七、数据类型格式化函数:

说明:
1、current_timestamp和now(卡塔尔(英语:State of Qatar)查询结果少年老成律
2、.263468+08 后边是精度p(6卡塔尔(英语:State of Qatar),后边是时区+08

PostgreSQL格式化函数提供大器晚成套卓有成效的工具用于把各个数据类型(日期/时间、integer、floating point和numeric卡塔尔(قطر‎调换到格式化的字符串以致反过来从格式化的字符串转变到钦命的数据类型。上面列出了这么些函数,它们都依照贰个共用的调用习贯:第三个参数是待格式化的值,而第三个是概念输出或输出格式的模板。

补充:
1、去精度
test=# select current_timestamp(0);

函数 重临类型 描述 例子 to_char(timestamp, text卡塔尔(قطر‎ text 把时间戳调换到字串 to_char(current_timestamp, 'HH12:MI:SS') to_char(interval, text卡塔尔(英语:State of Qatar) text 把时间间距转为字串 to_char(interval '15h 2m 12s', 'HH24:MI:SS') to_char(int, text卡塔尔(英语:State of Qatar) text 把整数转变成字串 to_char(125, '999') to_char(double precision, text卡塔尔(英语:State of Qatar) text 把实数/双精度数转换到字串 to_char(125.8::real, '999D9') to_char(numeric, text卡塔尔国 text 把numeric转变来字串 to_char(-125.8, '999D99S') to_date(text, text卡塔尔(قطر‎ date 把字串调换到日期 to_date('05 Dec 2000', 'DD Mon YYYY') to_timestamp(text, text卡塔尔国 timestamp 把字串转变届期间戳 to_timestamp('05 Dec 2000', 'DD Mon YYYY') to_timestamp(double卡塔尔国 timestamp 把UNIX纪元转换届期间戳 to_timestamp(200120400) to_number(text, text卡塔尔(英语:State of Qatar) numeric 把字串调换到numeric to_number('12,454.8-', '99G999D9S')

PostgreSQL教程(六):函数和操作符详解。      timestamptz      

2016-01-11 17:43:19+08
(1 row)

2、改动精度
test=# select current_timestamp(2);

  1. 用以日期/时间格式化的情势:

        timestamptz       

2016-01-11 17:45:06.26+08
(1 row)

3、去时区
test=# select current_timestamp(0)::timestamp without time zone;

模式 描述 HH 一天的时辰数(01-12卡塔尔 HH12 一天的时辰数(01-12卡塔尔(قطر‎ HH24 一天的小时数(00-23卡塔尔(قطر‎ MI 分钟(00-59卡塔尔(英语:State of Qatar) SS 秒(00-59卡塔尔(英语:State of Qatar) MS 微秒(000-999卡塔尔 US 阿秒(000000-999999卡塔尔国 AM 正午标志(大写卡塔尔(英语:State of Qatar) Y,YYY 带逗号的年(4和更加多位卡塔尔国 YYYY 年(4和更加的多位卡塔尔(قطر‎ YYY 年的后叁位 YY 年的后两位 Y 年的末尾壹位 MONTH 全长大写月份名(空白填充为9字符卡塔尔国 Month 全长混合大小写月份名(空白填充为9字符卡塔尔国 month 全长小写月份名(空白填充为9字符卡塔尔(قطر‎ MON 大写缩写月份名(3字符卡塔尔(英语:State of Qatar) Mon 缩写混合大小写月份名(3字符卡塔尔国 mon 小写缩写月份名(3字符卡塔尔(英语:State of Qatar) MM 月份号(01-12卡塔尔(قطر‎DAY 全长大写日期名(空白填充为9字符卡塔尔(英语:State of Qatar) Day 全长混合大小写日期名(空白填充为9字符卡塔尔(قطر‎ day 全长小写日期名(空白填充为9字符卡塔尔(قطر‎ DY 缩写大写日期名(3字符卡塔尔(قطر‎ Dy 缩写混合大小写日期名(3字符卡塔尔 dy 缩写小写日期名(3字符卡塔尔(قطر‎ DDD 一年里的光景(001-366卡塔尔(英语:State of Qatar) DD 一个月里的小日子(01-31卡塔尔 D 十五日里的生活(1-7;周末是1卡塔尔 W 叁个月里的周数(1-5卡塔尔国(第一周从上月第一天开首)WW 一年里的周数(1-53卡塔尔(قطر‎(第一周从该年的率后天开始卡塔尔国

      timestamp     

2016-01-11 17:43:50
(1 row)

4、使用cast函数做类型转变去掉时区
test=# select cast(current_timestamp(0) as timestamp without time zone); 

  1. 用来数值格式化的模版情势:

      timestamp     

2016-01-11 17:44:10
(1 row)

三、标准格式
1卡塔尔(قطر‎日期输入格式
January 8, 壹玖玖柒             在其它datestyle输入情势下都无歧义
1998-01-08             ISO-8601 格式,任何措施下都是壹玖玖捌年十15月8号
1/8/1997             歧义,在MDY下是孟陬八号;在 DMY 情势下读做九月10日
1/18/一九九六             在MDY格局下读做5月十四十二十三日,此外方式下被驳倒
01/02/03             MDY 格局下的二〇〇四年三月2日; DMY 形式下的 贰零零贰 年 十一月 1日; YMD 情势下的贰零零零年十一月四日;
壹玖玖陆-Jan-08             任何方式下都以菊月8日
Jan-08-1997             任何格局下都以夏正8日
08-Jan-壹玖玖捌             任何格局下都以暮商8日
99-Jan-08             在 YMD 形式下是无射8日,不然错误
08-Jan-99             11月五十12日,除了在 YMD 情势下是不当的之外
Jan-08-99             7月十八日,除了在 YMD 情势下是荒唐的之外
19990108             ISO-8601; 任何情势下都是1999年5月8日
990108                     ISO-8601; 任何形式下都以一九九七年十6月8日
1998.008             年和年里的第几天
J2451187             儒略日
January 8, 99 BC     公元前99年

有鉴于此,date最棒是使用1998-01-08、Jan-08-壹玖玖柒、19960108那三类格式,相比较通用。

2卡塔尔(قطر‎时间输入格式
04:05:06.789        ISO 8601
04:05:06        ISO 8601
04:05                ISO 8601
040506                ISO 8601
04:05 AM        与04:05均等;AM不影响数值
04:05 PM        与16:05同生龙活虎;输入小时数必须 <= 12
04:05:06.789-8        ISO 8601
04:05:06-08:00        ISO 8601
04:05-08:00        ISO 8601
040506-08        ISO 8601

time时间最常用的是04:05:06、04:05:06.789-8(加精度和时区)

3卡塔尔(英语:State of Qatar)时区输入格式
PST                 印度洋正式时间(Pacific Standard Time卡塔尔
America/New_York 完整时区名称
PST8PDT           POSIX风格的时区
-8:00                 ISO-8601与PST的偏移
-800                 ISO-8601与PST的偏移
-8                 ISO-8601与PST的偏移
zulu                 军方对UTC的缩写
z                 zulu的缩写

4卡塔尔国timestamp时间戳输入格式
岁月戳类型的实惠输入由一个日期和时间的连接组成,后边随着贰个可选的时区(以上三者的构成卡塔尔(英语:State of Qatar)因而,壹玖玖陆-01-08 04:05:06和1996-01-08 04:05:06 -8:00都以可行的数值。

5卡塔尔国interval时间间距输入格式
microseconds   微秒
milliseconds  毫秒
second    秒
minute  分钟
hour    小时
day   天
week   星期
month   月
year   年
decade   十年
century   世纪
millennium  千年
或这几个单位的缩写或复数。

interval格式举个例子:
1-2                        SQL规范格式:一年多少个月
3 4:05:06                SQL标准格式:3天4钟头5分6秒
1 year 2 months 3 days 4 hours 5 minutes 6 seconds  传统Postgres格式: 1年2个月3天4小时5分钟6秒
P1Y2M3DT4H5M6S                ISO 8601 "带标志符格式": 1年2个月3天4钟头5分钟6秒
P0001-02-03T04:05:06        ISO 8601 "缩写格式": 1年2个月3天4小时5分钟6秒

四、各类时间等级次序比方
test=# create table t1(id serial not null,name varchar(20),rtime date,stime timestamp,utime timestamp with time zone,wtime interval);
NOTICE:  CREATE TABLE will create implicit sequence "t1_id_seq" for serial column "t1.id"
CREATE TABLE

test=# insert into t1(name,rtime,stime,utime,wtime) values('nana','1/6/2015','2015-06-01 00:02:35','2015-06-01 00:02:35+08','1 day');
ERROR:  date/time field value out of range: "1/6/2015"
---报错:date类型不援救1/6/二零一六,因为会时有发生歧义
                                                             ^
HINT:  Perhaps you need a different "datestyle" setting.
test=# insert into t1(name,rtime,stime,utime,wtime) values('nana','2015-06-01','2015-06-01 00:02:35','2015-06-01 00:02:35+08','1 day');
INSERT 0 1
test=# insert into t1(name,rtime,stime,utime,wtime) values('tina','2015-11-01','2015-11-01 02:04:00','2015-11-01 02:04:00','6 days 3 hours 35 minutes');
INSERT 0 1
test=# insert into t1(name,rtime,stime,utime,wtime) values('lili','2014-12-24','2014-12-24 15:23:12','2014-12-24 15:23:12+08','1y3m5d');
INSERT 0 1
test=# select * from t1;
id | name |   rtime    |        stime        |         utime          |         wtime         
----+------+------------+---------------------+------------------------+------------------------
  1 | nana | 2015-06-01 | 2015-06-01 00:02:35 | 2015-06-01 00:02:35+08 | 1 day
  2 | tina | 2015-11-01 | 二零一六-11-01 02:04:00 | 2014-11-01 02:04:00+08 | 6 days 03:35:00         ---utime自动增添了时区
  3 | lili | 二零一四-12-24 | 二零一五-12-24 15:23:12 | 二〇一六-12-24 15:23:12+08 | 1 year 5 days 00:03:00   ---3m 被电动剖断为分钟minutes,并不是月months,由此照旧写现实一点比较好
(3 rows)

品种只写time约等于time with out time zone

五、相关函数
    PostgreSQL格式化函数提供意气风发套一蹴而就的工具用于把各个数据类型(日期/时间、integer、floating point和numeric卡塔尔转变到格式化的字符串
以致反过来从格式化的字符串调换来钦命的数据类型。

5.1 调换函数                              重回类型    描述                       例子
to_char(timestamp, text卡塔尔(英语:State of Qatar)          text        把时间戳调换到字串        to_char(current_timestamp, 'HH12:MI:SS')
to_char(interval, text卡塔尔(英语:State of Qatar)            text        把时间隔离转为字串        to_char(interval '15h 2m 12s', 'HH24:MI:SS')
to_char(int, text卡塔尔(قطر‎                text        把整数调换到字串          to_char(125, '999')
to_char(double precision, text卡塔尔国    text        把实数/双精度数转变到字串 to_char(125.8::real, '999D9')
to_char(numeric, text卡塔尔(英语:State of Qatar)            text        把numeric转变来字串      to_char(-125.8, '999D99S')
to_date(text, text卡塔尔(英语:State of Qatar)                date        把字串调换来日期          to_date('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(text, text卡塔尔(قطر‎          timestamp 把字串调换来时间戳        to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(double卡塔尔国              timestamp 把UNIX纪元转变届时间戳    to_timestamp(200120400)
to_number(text, text卡塔尔(قطر‎              numeric    把字串转变到numeric      to_number('12,454.8-', '99G999D9S')

实例:
postgres=# select current_timestamp,to_char(current_timestamp, 'YYYY-MM-DD HH24:MI'卡塔尔国;  ---时间戳调换到正规日期格式
              now              |     to_char     
-------------------------------+------------------
2016-01-12 17:18:17.993226+08 | 2016-01-12 17:18

postgres=# select to_char(interval '1 years 2months 5days 7hours 8minutes 9seconds', 'YYYY-MM-DD HH24:MI'卡塔尔;  ---时间间距转变届时间戳

模式 描述 9 带有钦赐数值位数的值 0 带前导零的值 .(句点卡塔尔国 小数点 ,(逗号卡塔尔(قطر‎ 分组(千卡塔尔国分隔符 PTucson 尖括号内负值 S 带符号的数值 L 货币符号 D 小数点 G 分组分隔符 MI 在指明的义务的负号(假设数字 0卡塔尔(英语:State of Qatar) PL 在指明的职位的正号(要是数字 0卡塔尔(英语:State of Qatar) SG 在指明的地点的正/负号

     to_char     

0001-02-05 07:08
postgres=# select to_char(125,'999'卡塔尔;  ---数字125转换来字符串'125'

八、时间/日期函数和操作符:

to_char

125
postgres=# select to_char(125.88,'999'卡塔尔;   ---双精度调换来字符串,要在乎精度

  1. 上边是PostgreSQL中帮忙的岁月/日期操作符的列表:

to_char

  126
postgres=# select to_char(125.88,'999D9');

操作符 例子 结果 + date '2001-09-28' + integer '7' date '2001-10-05' + date '2001-09-28' + interval '1 hour' timestamp '2001-09-28 01:00' + date '2001-09-28' + time '03:00' timestamp '2001-09-28 03:00' + interval '1 day' + interval '1 hour' interval '1 day 01:00' + timestamp '2001-09-28 01:00' + interval '23 hours' timestamp '2001-09-29 00:00' + time '01:00' + interval '3 hours' time '04:00' - - interval '23 hours' interval '-23:00' - date '2001-10-01' - date '2001-09-28' integer '3' - date '2001-10-01' - integer '7' date '2001-09-24' - date '2001-09-28' - interval '1 hour' timestamp '2001-09-27 23:00' - time '05:00' - time '03:00' interval '02:00' - time '05:00' - interval '2 hours' time '03:00' - timestamp '2001-09-28 23:00'

to_char

  125.9
postgres=# select to_char(125.88,'999D99');

  • interval '23 hours' timestamp '2001-09-28 00:00' - interval '1 day' - interval '1 hour' interval '23:00' - timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00' interval '1 day 15:00' * interval '1 hour' * double precision '3.5' interval '03:30' / interval '1 hour' / double precision '1.5' interval '00:40'

to_char

  125.88
postgres=# select to_char(-125.88,'999D99');