postgreSql——时区问题
timestamptz
timestamp
1 2 3 4 5 6 7 8 9 10 11 | SELECT ts AT TIME ZONE 'UTC' FROM ( VALUES (timestamptz '2012-03-05 17:00:00+0' ) , (timestamptz '2012-03-05 18:00:00+1' ) , ( timestamp '2012-03-05 18:00:00+1' ) -- ① loaded footgun! , ( timestamp '2012-03-05 11:00:00' AT TIME ZONE '+6' ) , ( timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC' ) , ( timestamp '2012-03-05 07:00:00' AT TIME ZONE 'US/Hawaii' ) -- ② , ( timestamp '2012-03-05 07:00:00' AT TIME ZONE 'HST' ) -- ② ) t(ts); |
1 2 3 | SELECT timestamptz '2012-03-05 20:00+03' , timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC' , timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC' AT TIME ZONE 'UTC' |
查看现在距1970-01-01 00:00:00 UTC 的秒数
1 | select extract(epoch from now()); |
1 | select timestamp without time zone 'epoch' , timestamp without time zone 'epoch' + 3600*interval '1 sec' |
1 | select timestamp without time zone 'epoch' , timestamp with time zone 'epoch' |
把epoch 值转换回时间戳
1 | select timestamp without time zone 'epoch' , timestamp with time zone 'epoch' , timestamp 'epoch' ,timestamptz 'epoch' |
mysql转为postgesql
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(UTC_TIMESTAMP()) + tz.gmt_offset, '%a, %d %b %Y, %H:%i:%s' ) AS local_time FROM "Dict" . "TimeZoneDetail" tz JOIN "Dict" . "TimeZoneToCountry" z ON tz.zone_id=z.zone_id WHERE tz.time_start <= UNIX_TIMESTAMP(UTC_TIMESTAMP()) AND z.zone_name= 'America/Los_Angeles' ORDER BY tz.time_start DESC LIMIT 1; SELECT (now() AT TIME ZONE 'UTC' )+interval '1' * tz. "GMTOffset" AS local_time,now() as now,now() AT TIME ZONE 'UTC' as utcnow,tz. "GMTOffset" as offsett FROM "Dict" . "TimeZoneDetail" tz inner JOIN "Dict" . "TimeZoneToCountry" z ON tz. "ZoneId" =z. "ZoneId" WHERE to_timestamp(tz. "TimeStart" ) <= timestamp 'epoch' AND z. "ZoneName" = 'America/Los_Angeles' ORDER BY tz. "TimeStart" DESC LIMIT 1; |
函数 | 返回类型 | 描述 |
示例 |
结果 |
age(timestamp, timestamp) | interval | 计算两个时间戳的时间间隔 |
select age(timestamp '2001-04-10', timestamp '1957-06-13'); |
43 years 9 mons 27 days |
age(timestamp) | interval | 计算current_date与入参时间戳的时间间隔 |
select age(timestamp '2016-07-07 12:00:00'); |
12:00:00 |
clock_timestamp() | timestamp with time zone | 当前时间戳(语句执行时变化) | select clock_timestamp(); | 2016-07-08 15:14:04.197732-07 |
current_date | date | 当前日期 | select current_date; | 2016-07-08 |
current_time | time with time zone | 当前时间 | select current_time; | 15:15:56.394651-07 |
current_timestamp | timestamp with time zone | 当前时间戳 | select current_timestamp; | 2016-07-08 15:16:50.485864-07 |
date_part(text, timestamp) | double precision | 获取时间戳中的某个子域,其中text可以为year,month,day,hour,minute,second等 |
select date_part('year',timestamp'2016-07-08 12:05:06'), date_part('month',timestamp'2016-07-08 12:05:06'), date_part('day',timestamp'2016-07-08 12:05:06'), date_part('minute',timestamp'2016-07-08 12:05:06'), date_part('second',timestamp'2016-07-08 12:05:06'); |
|
date_part(text, interval) | double precision | 功能同上,只是第二个入参为时间间隔 | select date_part('hour',interval'1 day 13:00:12'); | 13 |
date_trunc(text, timestamp) | timestamp |
将时间戳截断成指定的精度, 指定精度后面的子域用0补充 |
select date_trunc('hour', timestamp'2016-07-08 22:30:33'); |
2016-07-08 22:00:00 |
date_trunc(text, interval) | interval | 功能同上,只是第二个入参为时间间隔 | select date_trunc('hour',interval'1 year 2 mon 3 day 22:30:33'); | 1 year 2 mons 3 days 22:00:00 |
extract (field from timestamp) |
double precision | 功能同date_part(text, timestamp) | select extract(hour from timestamp'2016-07-08 22:30:29'); | 22 |
extract (field from interval) |
double precision | 功能同date_part(text, interval) | select extract(hour from interval'1 day 13:00:12'); | 13 |
isfinite(date) | boolean | 测试是否为有穷日期 | select isfinite(date'2016-07-08'),isfinite(date'infinity'); | t,f |
isfinite(timestamp) | boolean | 测试是否为有穷时间戳 | select isfinite(timestamp'2016-07-08'); | t |
isfinite(interval) | boolean | 测试是否为有穷时间间隔 | select isfinite(interval'1day 23:02:12'); | t |
justify_days(interval) | interval | 按照每月30天调整时间间隔 | select justify_days(interval'1year 45days 23:00:00'); | 1 year 1 mon 15 days 23:00:00 |
justify_hours(interval) | interval | 按照每天24小时调整时间间隔 | select justify_hours(interval'1year 45days 343hour'); | 1 year 59 days 07:00:00 |
justify_interval(interval) | interval | 同时使用justify_days(interval)和justify_hours(interval) | select justify_interval(interval'1year 45days 343hour'); | 1 year 1 mon 29 days 07:00:00 |
localtime | time | 当日时间 | select localtime; | 15:45:18.892224 |
localtimestamp | timestamp | 当日日期和时间 | select localtimestamp; | 2016-07-08 15:46:55.181583 |
make_date(year int, month int, day int) | date | 创建一个日期 | select make_date(2016,7,8); | 2016-07-08 |
make_interval( years int DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0.0) |
interval | 创建一个时间间隔 | select make_interval(1,hours=>3); | 1 year 03:00:00 |
make_time( hour int, min int, sec double precision) |
time | 创建一个时间 | select make_time(9,21,23); | 09:21:23 |
make_timestamp( year int, month int, day int, hour int, min int, sec double precision) |
timestamp | 创建一个时间戳 | select make_timestamp(2016,7,8,22,55,23.5); | 2016-07-08 22:55:23.5 |
make_timestamptz(year int, month int, day int, hour int, min int, sec double precision, [ timezone text ]) |
timestamp with time zone | 创建一个带有时区的时间戳 | select make_timestamptz(2016,7,8,22,55,23.5); | 2016-07-08 22:55:23.5-07 |
now() | timestamp with time zone | 当前日期和时间 | select now(); | 2016-07-08 15:55:30.873537-07 |
statement_timestamp() | timestamp with time zone | 同now() | select statement_timestamp(); | 2016-07-08 15:56:07.259956-07 |
timeofday() | text |
当前日期和时间,包含周几, 功能与clock_timestamp()类似 |
select timeofday(); | Fri Jul 08 15:57:51.277239 2016 PDT |
transaction_timestamp() | timestamp with time zone | 事务开始时的时间戳 | select transaction_timestamp(); | 2016-07-08 16:01:25.007153-07 |
to_timestamp(double precision) | timestamp with time zone |
Convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp |
select to_timestamp(1284352323); |
2010-09-12 21:32:03-07 |
pg_sleep(seconds double precision); |
当前会话休眠seconds秒 |
select pg_sleep(5); | ||
pg_sleep_for(interval) | 当前会话休眠多长时间的间隔 | select pg_sleep_for('5 seconds'); | ||
pg_sleep_until(timestamp with time zone) | 当前会话休眠至什么时间点 | select pg_sleep_until('2016-07-08 23:59:59'); |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 没有源码,如何修改代码逻辑?
· NetPad:一个.NET开源、跨平台的C#编辑器
· 面试官:你是如何进行SQL调优的?