PostgreSQL中timestamp相关的SQL语句
发布时间:2020-12-13 17:18:45 所属栏目:百科 来源:网络整理
导读:获取时区 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 获取时区的分钟数 s
获取时区 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 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |