How to insert time without fractional seconds?

Question

When I insert the time in my table with the current_time function I get this: 16:42:41.095644.

But I want this: 16:42:41 or this: 16:42.

Is there any way to this? It might not even be with current_time, I can use any other function, but how do I do I insert time like that?

Answer 1

I never use CURRENT_TIME. It returns the type time with time zone, the use of which is discouraged in the Postgres manual because it's an inherently broken construct. The type (and the function CURRENT_TIME) are only supported because standard SQL sadly defined them this way. See:

• Comparing TIME WITH TIME ZONE returns unexpected result

• Postgres time with time zone equality

LCOALTIME (returning timestamp) makes more sense. But you can just use now() (= CURRENT_TIMESTAMP) for everything. Local time always depends on the current time zone setting of the session anyway. See:

• Difference between now() and current_timestamp
• Ignoring time zones altogether in Rails and PostgreSQL

Of course, you can cast timestamp or timstamptz to time:

To round to full seconds:

INSERT INTO my_tbl(my_time_col)
VALUES (LOCALTIME::time(0)::time);

To truncate to seconds or minutes, ...

date_trunc('second', LOCALTIMESTAMP)::time
date_trunc('minute', LOCALTIMESTAMP)::time

This is equivalent:

date_trunc('second', LOCALTIME)::time
date_trunc('minute', LOCALTIME)::time

The expression date_trunc('second', LOCALTIME) returns interval, so the cast to time may or may not be needed. In an INSERT writing to a time column, the type is coerced automatically.

date_trunc('second', LOCALTIMESTAMP) returns timestamp. The explicit cast to time may still be optional for the same reason.

Related:

• How to round off milliseconds value from timestamp(0) in PostgreSQL?

Answer 2

time is stored using an internal format.

But you can convert a timestamp to a string, but not a time to a string directly. So one workaround is to add a date component:

select to_char('2000-01-01'::date + current_time, 'HH24:MI')

Answer 3

current_date https://www.postgresql.org/docs/9.1/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

Note: Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the "current" time, so that multiple modifications within the same transaction bear the same time stamp.