PostgreSQL中timestamp相关的SQL语句

获取时区

select extract(timezone from timestamp with time zone '2001-02-16 20:38:40.12-08');
Result: 28800

获取时区的小时数

select extract(timezone_hour from timestamp with time zone '2001-02-16 20:38:40.12-08');
Result: 8

获取时区的分钟数

select extract(timezone_minute from timestamp with time zone '2001-02-16 20:38:40.12-08');
Result：0

获取世纪

select extract(century from timestamp '2000-12-16 12:21:13');
Result: 20

select extract(century from timestamp '2001-02-16 20:38:40');
Result: 21

获取年份

select extract(year from timestamp '2001-02-16 20:38:40');
Result: 2001

获取季度（1-4）

select extract(quarter from timestamp '2001-02-16 20:38:40');
Result: 1

获取 timestamp 的月数（1-12） & interval 的月数对12取模（0-11）

select extract(month from timestamp '2001-02-16 20:38:40');
Result: 2

select extract(month from interval '2 years 3 months');
Result: 3

select extract(month from interval '2 years 13 months');
Result: 1

获取周数

select extract(week from timestamp '2001-02-16 20:38:40');
Result: 7

获取 timestamp 的天数（1-31） & interval 的总天数

select extract (day from timestamp '2001-02-16 20:38:40');
Result: 16

select extract(day from interval '40 days 1 minute');
Result: 40

获取 timestamp 的天数（Sunday(0) - Saturday(6)）

select extract(dow from timestamp '2001-02-16 20:38:40');
Result: 5

获取 timestamp 的天数（Monday(1) - Sunday(7)）

select extract(isodow from timestamp '2001-02-18 20:38:40');
Result: 7

获取 timestamp 的天数（1-365/366）

select extract(doy from timestamp '2001-02-16 20:38:40');
Result: 47

获取小时（0-23）

select extract(hour from timestamp '2001-02-16 20:38:40');
Result: 20

获取分钟（0-59）

select extract(minute from timestamp '2001-02-16 20:38:40');
Result: 38

获取秒（0-59）

select extract(second from timestamp '2001-02-16 20:38:40');
Result: 40

select extract(second from time '17:12:28.5');
Result: 28.5

获取总秒数（对于 timestamp 从 1970-01-01 00:00:00 UTC 开始计算）

select extract(epoch from timestamp with time zone '2001-02-16 20:38:40.12-08');
Result: 982384720.12

select extract(epoch from interval '5 days 3 hours');
Result: 442800