Question 88827 in PostgreSQL Question posted in
The official documentation can be found here.

How I can trim seconds out of a time field in postgresql?

In my postgresql I have the following table:

Name: mytable
---
id INT PRIMARY KEY,
time TIME WITHOUT TIME ZONE

And I select some info:

select id,time from mytable;

But I want the time to be formated without seconds. How I can do this?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    According to postgresql documentation you need to use the to_char function.

    In your case you'll need to do:

    1. For 12h formated time:
    select id,to_char(time,'HH:MI') as time from mytable;

    OR

    select id,to_char(time,'HH:MI') as time from mytable;
    1. For 24h format:
    select id,to_char(time,'HH24:MI') as time from mytable;

    This is applicable to all postgresql versions. I tested it on Postgresql 11.


  2. 0

    As answered by Dimitrios Desyllas , using "to_char" is the best way.

    Alternatively, the same can also by achieved by "date_trunc" function. It truncates the value to the nearest minute.

    This can be done as:

    SELECT id, date_trunc('minute', time) as new_time FROM mytable;

    Another way of achieving this is by "Substring", this can be done as:

    SELECT id, CONCAT(SUBSTRING(time::text, 1, 2), ':', SUBSTRING(time::text, 4, 2)) as new_time FROM mytable;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search