Postgre SQL 中的时间格式

我的其他文章

• 亲身分享 一次 字节跳动 真实面试经历和面试题

时间格式

时间单位：
year :年
week ：该天在所在的年份里是第几周
timezone_minute：时区偏移量的分钟部分
timezone_hour：时区偏移量的小时部分
timezone：与UTC的时区偏移量，以秒记。正数对应 UTC 东边的时区，负数对应 UTC 西边的时区
second ：秒
quarter：日期中年所在季度(1-4)
month：月(0-11)
minute：分钟(0-59)
milliseconds：
isodow：周中的第几天 [1-7] 星期一：1) 星期天：(7)
dow：周中天的索引(0-6 ；星期天是 0)
doy：一年的第几天(1-365/366)
hour：小时(0-23)
day: 天(1-31)
 
 
模式描述
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)(第一周从该年的第一天开始)
计算相差天数

时间函数

date_trunc函数

date_trunc('day',now()) --获取的是当前天的
date_trunc('week',now()) --获取的是本周的初始日期，也就是本周星期一的日期
date_trunc('week',date_trunc('week',now())+ interval '6 days' )+ interval '6 days' --获得本周最后一天，也就是周日的日期
date_trunc('month',now()) --获取的是当月的初始日期，也就是1号
date_trunc('month',now() + interval '1 months') - interval '1 days' --获得本月最后一天
date_trunc('year',now()) --获取的是当年的初始日期，也就是本年的1月1号
date_trunc('year',now() + interval '12 months') - interval '1 days' --获得本年的最后1天
 
select date_trunc('month',now()) +interval '12 h';		
//每月1号 12点		
select date_trunc('month',now()) + interval '15 d 9 h 30 min';		
//每月15号9点半		
select date_trunc('day',now()) + interval '9 h';		
//每天9点		
select date_trunc('day',now()) + interval '7 d';		
//每周的今天		
select date_trunc('weak',now())  + interval '1d 1minute';		
//每周的周二第一分钟		
select date_trunc('h',now()) + interval '30 minute';		
//每小时		
select date_trunc('minute',now()) + interval '30 second';		
//每分钟		
select date_trunc('minute',now()) + interval '30 minute 30 second';		
//每30分钟 			
select date_trunc('quarter',now()) + interval '15 d 15 h 15 minute 30 second';		
//本季度的第15天,15小时 15分 30秒		
select date_trunc('quarter',now() ) - interval '1 h';		
//每个季度最后一天的晚上11点		
select date_trunc('quarter',now() + interval '3 month') - interval '1 h';		
//每个季度的最后一天的晚上的11点(从下个季度开始算起).	

timestamp函数

SELECT now()::timestamp + '1 year';
SELECT now()::timestamp + '1 month';
SELECT now()::timestamp + '1 day';
SELECT now()::timestamp + '1 hour';
SELECT now()::timestamp + '1 min';
SELECT now()::timestamp + '1 sec';
select now()::timestamp + '1 year 1 month 1 day 1 hour 1 min 1 sec';
SELECT now()::timestamp + (col || ' day')::interval FROM table

下面的SQL文查询结果是 “2018-08-20 10:09:10.815125”，

并且返回类型可以当String处理。返回json等都方便使用。

SQL> SELECT to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS');

更新时，参数传入“2018-08-20 10:09:10.815125”的字符串，

那么需要在SQL中转化来匹配updateTime字段的timeStamp数据类型。

SQL> update tbl_A set username='XXX' where userid='001' and updateTime = to_timestamp('2018-08-20 10:09:10.815125','yyyy-mm-dd hh24:mi:ss.us');

函数 返回类型 描述 例子

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

时间计算

--计算天
select date_part('day', '2015-01-15 17:05'::timestamp - '2013-01-14 16:05'::timestamp);
 
--计算天
select extract(day FROM (age('2017-12-10 01:10:20'::TIMESTAMP, '2017-12-01 00:00:00'::TIMESTAMP))) || '天'
 
--计算小时
select extract(hour FROM (age('2017-12-10 01:10:20'::TIMESTAMP, '2017-12-01 00:00:00'::TIMESTAMP))) || '小时'
 
--计算分钟
select extract(minute FROM (age('2017-12-10 01:10:20'::TIMESTAMP, '2017-12-01 00:00:00'::TIMESTAMP))) || '分钟'
 
--计算秒
select extract(second FROM (age('2017-12-10 01:10:20'::TIMESTAMP, '2017-12-01 00:00:00'::TIMESTAMP))) || '秒'
 
--计算时间差秒数
select extract(epoch FROM (now() - (now()-interval '1 day') ));
 
select extract(epoch FROM (now() - (now()-interval '1 day') ));
select trunc(extract(epoch FROM (now() - (now()-interval '1 day') ))::numeric);
select trunc(extract(epoch FROM (now() - (now()-interval '1 day') ))::numeric,1);
select round(extract(epoch FROM (now() - (now()-interval '1 day') ))::numeric);
select round(extract(epoch FROM (now() - (now()-interval '1 day') ))::numeric,1);

在PostgreSQL中可以直接对时间进行加减运算：

SELECT now()::timestamp + '1 year';  --当前时间加1年
SELECT now()::timestamp + '1 month';  --当前时间加一个月
SELECT now()::timestamp + '1 day';  --当前时间加一天
SELECT now()::timestamp + '1 hour';  --当前时间加一个小时
SELECT now()::timestamp + '1 min';  --当前时间加一分钟
SELECT now()::timestamp + '1 sec';  --加一秒钟
select now()::timestamp + '1 year 1 month 1 day 1 hour 1 min 1 sec';  --加1年1月1天1时1分1秒
SELECT now()::timestamp + (col || ' day')::interval FROM table --把col字段转换成天 然后相加

我的其他文章

亲身分享 一次 字节跳动 真实面试经历和面试题

我的网站

字节小柜：http://82.157.190.245/