Extract weekday, millisecond, microsecond, nanosecond from a date in Postgres

Question

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.

Answer 1

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

• 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

(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

Answer 2

https://www.postgresql.org/docs/current/static/datatype-datetime.html, look at the "Table 8-9. Date/Time Types" Date type resolution 1 day. so you can't get none of above, but the weekday:

t=# select extract(Dow from now()::date);
 date_part
-----------
         1
(1 row)

In case you were looking for abbreviation from date, not the day of week number:

t=# select to_char(now()::date,'Dy');
 to_char
---------
 Mon
(1 row)

For more details look at docs:

Dy - abbreviated capitalized day name (3 chars in English, localized lengths vary)

(formatting mine)

Answer 3

As far as I know Postgres doesn't support nanoseconds. For the rest:

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

| date_part |
| :-------- |
| 5         |

select extract(milliseconds from timestamp '2001-02-16 20:38:40.5');

| date_part |
| :-------- |
| 40500     |

select extract(microseconds from timestamp '2001-02-16 20:38:40.5');

| date_part |
| :-------- |
| 40500000  |

dbfiddle here