CODEWITHSUNDEEP
postgresql
EvaHHHH
EvaHHHH

Reputation: 353

Why my PostgreSQL didn't show millisecond in CURRENT_TIMESTAMP?

I try to use CURRENT_TIMESTAMP to get date time with millisecond.

But I only can retrieve to second as below.

Did I miss something?

enter image description here

Upvotes: 2

Views: 2929

Answers (2)

Stefanov.sm
Stefanov.sm

Reputation: 13049

This is a presentation issue. Postgres' timestamps do include milliseconds. You can format the timestamp yourself to include milliseconds and/or extract the milliseconds value like this:

select to_char(current_timestamp, 'yyyy-mm-dd hh24:mi:ss.us') as ts, 
       extract (milliseconds from current_timestamp) as ms;

-- result: 
-- ts                        |ms       |
-- --------------------------+---------+
-- 2021-07-04 12:23:39.102346|39102.346|

Upvotes: 5

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

The limitation you are seeing is due to the client tool you are using. The CURRENT_TIMESTAMP does have precision down to fractions of a millisecond. To confirm this, run the following query:

SELECT EXTRACT(epoch FROM current_timestamp);  -- 1625390722.93962

You should see the number of milliseconds since Jan 1, 1970.

Upvotes: 2

Related Questions