PostgreSQL数据类型格式化函数操作

数据类型格式化函数：

PostgreSQL格式化函数提供一套有效的工具用于把各种数据类型(日期/时间、integer、floating point和numeric)转换成格式化的字符串以及反过来从格式化的字符串转换成指定的数据类型。

下面列出了这些函数，它们都遵循一个公共的调用习惯：第一个参数是待格式化的值，而第二个是定义输出或输出格式的模板。

函数 返回类型 描述 例子
to_char(timestamp, text) text 把时间戳转换成字串 to_char(current_timestamp, ‘HH12:MI:SS')
to_char(interval, text) 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) 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) numeric 把字串转换成numeric to_number(‘12,454.8-‘, ‘99G999D9S')

1. 用于日期/时间格式化的模式：

模式 描述
HH 一天的小时数(01-12)
HH12 一天的小时数(01-12)
HH24 一天的小时数(00-23)
MI 分钟(00-59)
SS 秒(00-59)
MS 毫秒(000-999)
US 微秒(000000-999999)
AM 正午标识(大写)
Y,YYY 带逗号的年(4和更多位)
YYYY 年(4和更多位)
YYY 年的后三位
YY 年的后两位
Y 年的最后一位
MONTH 全长大写月份名(空白填充为9字符)
Month 全长混合大小写月份名(空白填充为9字符)
month 全长小写月份名(空白填充为9字符)
MON 大写缩写月份名(3字符)
Mon 缩写混合大小写月份名(3字符)
mon 小写缩写月份名(3字符)
MM 月份号(01-12)
DAY 全长大写日期名(空白填充为9字符)
Day 全长混合大小写日期名(空白填充为9字符)
day 全长小写日期名(空白填充为9字符)
DY 缩写大写日期名(3字符)
Dy 缩写混合大小写日期名(3字符)
dy 缩写小写日期名(3字符)
DDD 一年里的日子(001-366)
DD 一个月里的日子(01-31)
D 一周里的日子(1-7；周日是1)
W 一个月里的周数(1-5)(第一周从该月第一天开始)
WW 一年里的周数(1-53)(第一周从该年的第一天开始)

2. 用于数值格式化的模板模式：

模式 描述
9 带有指定数值位数的值
0 带前导零的值
.(句点) 小数点
,(逗号) 分组(千)分隔符
PR 尖括号内负值
S 带符号的数值
L 货币符号
D 小数点
G 分组分隔符
MI 在指明的位置的负号(如果数字 < 0)
PL 在指明的位置的正号(如果数字 > 0)
SG 在指明的位置的正/负号

时间/日期函数和操作符：

1. 下面是PostgreSQL中支持的时间/日期操作符的列表：

操作符 例子 结果
+ 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' - 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'

2. 日期/时间函数：

函数 返回类型 描述 例子 结果
age(timestamp, timestamp) interval 减去参数，生成一个使用年、月的”符号化”的结果 age(‘2001-04-10', timestamp ‘1957-06-13') 43 years 9 mons 27 days
age(timestamp) interval 从current_date减去得到的数值 age(timestamp ‘1957-06-13') 43 years 8 mons 3 days
current_date date 今天的日期
current_time time 现在的时间
current_timestamp timestamp 日期和时间
date_part(text, timestamp) double 获取子域(等效于extract) date_part(‘hour', timestamp ‘2001-02-16 20:38:40') 20
date_part(text, interval) double 获取子域(等效于extract) date_part(‘month', interval ‘2 years 3 months') 3
date_trunc(text, timestamp) timestamp 截断成指定的精度 date_trunc(‘hour', timestamp ‘2001-02-16 20:38:40') 2001-02-16 20:00:00+00
extract(field from timestamp) double 获取子域 extract(hour from timestamp ‘2001-02-16 20:38:40') 20
extract(field from interval) double 获取子域 extract(month from interval ‘2 years 3 months') 3
localtime time 今日的时间
localtimestamp timestamp 日期和时间
now() timestamp 当前的日期和时间(等效于 current_timestamp)
timeofday() text 当前日期和时间

3. EXTRACT，date_part函数支持的field：

域 描述 例子 结果
CENTURY 世纪 EXTRACT(CENTURY FROM TIMESTAMP ‘2000-12-16 12:21:13'); 20
DAY (月分)里的日期域(1-31) EXTRACT(DAY from TIMESTAMP ‘2001-02-16 20:38:40'); 16
DECADE 年份域除以10 EXTRACT(DECADE from TIMESTAMP ‘2001-02-16 20:38:40'); 200
DOW 每周的星期号(0-6；星期天是0) (仅用于timestamp) EXTRACT(DOW FROM TIMESTAMP ‘2001-02-16 20:38:40'); 5
DOY 一年的第几天(1 -365/366) (仅用于 timestamp) EXTRACT(DOY from TIMESTAMP ‘2001-02-16 20:38:40'); 47
HOUR 小时域(0-23) EXTRACT(HOUR from TIMESTAMP ‘2001-02-16 20:38:40'); 20
MICROSECONDS 秒域，包括小数部分，乘以 1,000,000。 EXTRACT(MICROSECONDS from TIME ‘17:12:28.5'); 28500000
MILLENNIUM 千年 EXTRACT(MILLENNIUM from TIMESTAMP ‘2001-02-16 20:38:40'); 3
MILLISECONDS 秒域，包括小数部分，乘以 1000。 EXTRACT(MILLISECONDS from TIME ‘17:12:28.5'); 28500
MINUTE 分钟域(0-59) EXTRACT(MINUTE from TIMESTAMP ‘2001-02-16 20:38:40'); 38
MONTH 对于timestamp数值，它是一年里的月份数(1-12)；对于interval数值，它是月的数目，然后对12取模(0-11) EXTRACT(MONTH from TIMESTAMP ‘2001-02-16 20:38:40'); 2
QUARTER 该天所在的该年的季度(1-4)(仅用于 timestamp) EXTRACT(QUARTER from TIMESTAMP ‘2001-02-16 20:38:40'); 1
SECOND 秒域，包括小数部分(0-59[1]) EXTRACT(SECOND from TIMESTAMP ‘2001-02-16 20:38:40'); 40
WEEK 该天在所在的年份里是第几周。 EXTRACT(WEEK from TIMESTAMP ‘2001-02-16 20:38:40'); 7
YEAR 年份域 EXTRACT(YEAR from TIMESTAMP ‘2001-02-16 20:38:40'); 2001

4. 当前日期/时间：

我们可以使用下面的函数获取当前的日期和/或时间∶

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME (precision)
CURRENT_TIMESTAMP (precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME (precision)
LOCALTIMESTAMP (precision)

文中提供的所有信息均源自PostgreSQL官方文档，编写该篇文章的主要目的是便于今后的查阅，特此声明。

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。如有错误或未考虑完全的地方，望不吝赐教。

赞 (0)

相关推荐

  • Postgresql 数据库 varchar()字符占用多少字节介绍

    如下所示: create table tmp1 ( name varchar(3) ); select pg_size_pretty(pg_relation_size('tmp1')); -- 0 bytes insert into tmp(name) values('欧阳子'); -- 8192 bytes 8192 bytes = 8KB = 1页(数据库的最小单位) 8页 = 1区 = 64KB(记不清磁头一次取1区还是1页数据了) pg_relation_size()函数用于查询表占用空

  • postgresql 导出建表语句的命令操作

    postgresql 导出建表语句的命令操作

    我就废话不多说了,大家还是直接看命令吧~ pg_dump -h host -U postgres -d dbname -O -s -t "table_name" > name.sql 补充:PostgreSQL使用pg_dump导出单个数据表的insert语句 PostgreSQL自带的pgadmin4工具没有办法生成数据表的insert语句,这使得想要提取单个表的数据,插入到不同环境的相同表中变成很麻烦的一件事,使用PostgreSQL的pg_dump工具可以实现该功能. 使用

  • postgreSQL 数字与字符串类型转换操作

    数字转字符串 select cast(123 as VARCHAR); 字符串转数字 select cast('123' as INTEGER); 以此类推,应该也可以转换为其他数据类型. 补充:pgSql, mySql中字符串转化为数字 pgSql 语法 to_number(text, text) 例子 select to_number(trim(both 'ibs' from classname), '999999') as cn from bbs order by cn /*trim(bo

  • PostgreSQL标准建表语句分享

    如下所示: -- 建表 CREATE TABLE if not exists public.user ( id character varying(32) NOT NULL DEFAULT sys_guid(), name character varying(100) NOT NULL, gender character varying(50) NOT NULL, age character varying(10) NOT NULL, id_no character varying(50) NO

  • postgresql修改自增序列操作

    在 mysql 中,实现 id 自增的方式是依靠加一个 auto_increment 标志,而在 pgsql 中,id 自增是通过序列 SEQUENCE. 创建表时设置自增序列为: CREATE TABLE "config" ( "id" int4 NOT NULL DEFAULT nextval('config_id_seq'::regclass), ... }; 如果需要修改创建表之后的序列,如: ----删除前先解除 id 对该序列的依赖 ALTER TABL

  • 解决postgresql 数字转换成字符串前面会多出一个空格的问题

    解决postgresql 数字转换成字符串前面会多出一个空格的问题

    postgresql中将数字转换为字符串前面莫名期末的多出一个空格, 例如: 前面加上fm就可以去掉空格了,如下 补充:postgresql去掉字符串空白符以及首尾空格 我就废话不多说了,大家还是直接看代码吧~ --去除空白符 select regexp_replace(' a s d ', E'\\s+', ' ', 'g') --去除首空格 select regexp_replace(' a b d ',E'(^\\s*)','','g') --去除尾空格 select regexp_rep

  • postgresql 实现更新序列的起始值

    迁移老数据到新的数据库,需要把id迁移过来,这时序列值就需要修改为当前最大值加1了. 使用这个语句就可以让s_seq的序列值从7开始了. alter sequence s_seq restart with 7; 补充:PostgreSQL设置主键自增.PostgreSQL重置主键自增从1开始,PostgreSQL清空表数据自增从1开始 PostgreSQL设置主键自增.PostgreSQL重置主键自增从1开始 自增字段 从1开始 清空表数据,自增从1开始 自增字段 PostgreSQL设置主键自

  • PostgreSQL数据类型格式化函数操作

    数据类型格式化函数: PostgreSQL格式化函数提供一套有效的工具用于把各种数据类型(日期/时间.integer.floating point和numeric)转换成格式化的字符串以及反过来从格式化的字符串转换成指定的数据类型. 下面列出了这些函数,它们都遵循一个公共的调用习惯:第一个参数是待格式化的值,而第二个是定义输出或输出格式的模板. 函数 返回类型 描述 例子 to_char(timestamp, text) text 把时间戳转换成字串 to_char(current_timest

  • PHP常用函数之格式化时间操作示例

    本文实例讲述了PHP常用函数之格式化时间操作.分享给大家供大家参考,具体如下: /** * 格式化时间 * @param $time 时间戳 * @return bool|string */ function formatTimeToNow($time){ //获取现在的时间戳 $nowtime = time(); if($time>$nowtime){ return ''; }else{ $tc = $nowtime-$time; if( $tc >= 864000 ){ $str = da

  • MySQL日期函数与日期转换格式化函数大全

    Mysql作为一款开元的免费关系型数据库,用户基础非常庞大,本文列出了MYSQL常用日期函数与日期转换格式化函数 1.DAYOFWEEK(date) SELECT DAYOFWEEK('2016-01-16') SELECT DAYOFWEEK('2016-01-16 00:00:00') -> 7 (表示,记住:星期天=1,星期一=2, ... 星期六=7) 2.WEEKDAY(date) SELECT WEEKDAY('2016-01-16') SELECT WEEKDAY('2016-01

  • JavaScript自定义日期格式化函数详细解析

    我们对 JavaScript 扩展其中一个较常的做法便是对 Date.prototype 的扩展.因为我们知道,Date 类只提供了若干获取日期元素的方法,如 getDate(),getMinute()--却没有一个转换为特定字符串的格式化方法.故所以,利用这些细微的方法,加以封装,组合我们想要的日期字符串形式.一般来说,该格式化函数可以定义在 Date 对象的原型身上,也可以独立一个方法写出.定义原型方法的操作如 Date.prototype.format = function(date){-

  • PHP中常用的字符串格式化函数总结

    PHP中常用的字符串格式化函数总结

    字符串的格式化就是将字符串处理为某种特定的格式.通常用户从表单中提交给服务器的数据都是字符串的形式,为了达到期望的输出效果,就需要按照一定的格式处理这些字符串后再去使用.经常见到的字符串格式化函数如下图所示: 注意:在PHP中提供的字符串函数处理的字符串,大部分都不是在原字符串上修改,而是返回一个格式化后的新字符串. 一.取出空格和字符串填补函数 空格也是一个有效的字符,在字符串中也会占据一个位置.用户在表单输入数据时,经常在无意中会多输入一些无意义的空格.因此PHP脚本在接收到通过表单处理过来

  • Python数据类型和常用操作

    Python数据类型和常用操作

    目录 一.数值 二.字符串 三.列表 四.元组 五.字典 六.集合 一.数值 数值数据类型用于存储数值,数据类型不可改变,改变会分配一个新的对象,在Python中,“与或非”布尔操作使用关键词and/or/not. 基本常用操作代码: print(True+False) #输出为1,True默认为1,False为0 print(True or False) #输出True,关键字or执行"或"操作 print(5//2) #输出2,//为取整运算符 print(5%2) #输出1,%为

  • MySQL中时间函数操作大全

    MySQL中时间函数操作大全

    目录 1. 获取系统当前时间 1.1. 获取 YYYY-MM-DD HH:mm:ss 1.2. 获取 YYYY-MM-DD 1.3. 获取 HH:mm:ss 2. 时间加减间隔函数 2.1. DATETIME 类型的加减 2.2. DATE 类型的加减 3. 两个时间的相减 3.1. DATE 类型相减 3.2. TIMESTAMP 类型 附:时间差 datediff() 总结 1. 获取系统当前时间 MySQL 版本为 5.7,详细的时间函数可以参考 MySQL 官方文档 在这里 1.1. 获

  • ASP常用日期格式化函数 FormatDate()

    核心代码 <% '功能:多功能日期格式化函数 '来源:http://jorkin.reallydo.com/article.asp?id=477 Function FormatDate(sDateTime, sReallyDo) Dim sJorkin sJorkin = GetLocale() If Not IsDate(sDateTime) Then sDateTime = Now() sDateTime = CDate(sDateTime) Select Case UCase(sReall

  • PostgreSQL 正则表达式 常用函数的总结

    PostgreSQL 正则表达式 常用函数的总结 对那些需要进行复杂数据处理的程序来说,正则表达式无疑是一个非常有用的工具.本文重点在于阐述 PostgreSQL 的一些常用正则表达式函数以及源码中的一些函数. 正则相关部分的目录结构 [root@localhost regex]# pwd /opt/hgdb-core/src/include/regex [root@localhost regex]# ll total 40 -rw-r--r--. 1 postgres postgres 349

  • python3.4用函数操作mysql5.7数据库

    本文实例为大家分享了python3.4函数操作mysql数据库的具体代码,供大家参考,具体内容如下 #!/usr/bin/env python # -*- coding:utf-8 -*- # __author__ = "blzhu" """ python study Date:2017 """ # -*- coding: utf-8 -*- __author__ = 'djstava@gmail.com' import lo

随机推荐