从Postgres中的日期中提取工作日，毫秒，微秒，纳秒 [英] Extract weekday, millisecond, microsecond, nanosecond from a date in Postgres

问题描述

如何从Postgres中的日期提取毫秒，工作日，微秒，纳秒。我也尝试过提取方法，但是找不到完全等效的方法。

How to extract the millisecond, weekday, microsecond, nanosecond from a date in Postgres. I have tried the extract methods too, but I could not find an exact equivalent.

推荐答案

我不确定是什么等效方法您正在寻找，但是：

I'm not sure what equivalent are you looking for, but:

• 在PostgreSQL中没有纳秒精度： p （精度）的允许范围为0到6，对于时间戳和间隔类型。


• 某些日期部分包括其他日期部分：即毫秒包含秒& microseconds 包含毫秒（因此也包含 seconds ）。

• there is no nanosecond precision in PostgreSQL: The allowed range of p (precision) is from 0 to 6 for the timestamp and interval types.
• some date parts include others: i.e. milliseconds contains seconds & microseconds contains milliseconds (and thus seconds too).

如果要查找逻辑上分开的值，则需要做一些数学运算，例如：

If you are looking for logically separate values, you'll need to do some math, e.g.:

select extract(dow from ts) dow,       -- day-of-week (where weeks start on sunday, which is 0)
       extract(isodow from ts) isodow, -- ISO day-of-week (where weeks start on monday, which is 1)
       floor(extract(seconds from ts))::int only_seconds,
       floor(extract(milliseconds from ts))::int - 1000 * floor(extract(seconds from ts))::int only_milliseconds,
       floor(extract(microseconds from ts))::int - 1000 * floor(extract(milliseconds from ts))::int only_microseconds,
       extract(microseconds from ts) all_microseconds

如果要查看时间戳在其实际周内的距离，可以使用时间戳（和间隔）算法也是：

Or, if you are looking to how far a timestamp is within its actual week, you can use timestamp (and interval) arithmetics too:

select ts - date_trunc('week', ts) time_elapsed_since_monday

（尽管很难计算从星期日开始的几周： date_trunc 仅适用于ISO周）。

(Although it is rather hard to calculate this for weeks which start on sunday: date_trunc works with only ISO weeks).

http://rextester.com/SOOO48159

这篇关于从Postgres中的日期中提取工作日，毫秒，微秒，纳秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！