3

I have following table timestamp(6) without timezone

2000/01/01 0:00:00
2000/01/01 10:00:00
2000/01/01 04:00:00

I would like to get hh:mm:ss My desired result is like follows

0:00:00
10:00:00
04:00:00

Are there any good ways of doing this?

Dharman
  • 30,962
  • 25
  • 85
  • 135
Heisenberg
  • 4,787
  • 9
  • 47
  • 76
  • 1
    [`to_char(col,'HH:MI:SS')`](https://www.postgresql.org/docs/current/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE) – Zegarek Nov 14 '22 at 06:33
  • Not sure what happened here. This question clearly asks about dropping the date part and specifies output format. It's not a duplicate of the thread linked as duplicate, nor can we find an answer in that thread. Also, there was a good answer here that proposed `::time`, keeping a more flexible data type than `to_char()` - only short of rounding `::time(0)` to be perfect. – Zegarek Nov 14 '22 at 06:52
  • will `col - trunc(col)` work? – tnavidi Nov 14 '22 at 08:26
  • @tnavidi [`col - date_trunc('seconds',col)`](https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC) will return the truncated fractions of a second, not the time without them. – Zegarek Nov 14 '22 at 09:38

1 Answers1

4

  1. to_char(col,'HH:MI:SS') if you're only extracting to text. The function gives you a lot of formatting flexibility and truncates/discards everything after seconds, without rounding.

  2. To trim leading zero like in presented desired results, you can add an FM fill mode modifier prefix to desired fields in to_char().

  3. col::time(0) will also keep the time data type allowing you to use adequate functions and operators:

    time, timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds field.

    Set to 0 it will round fractions of a second. It was initially suggested by someone else - best choice in my opinion - but the answer was removed when thread got falsely discarded as duplicate.

  4. date_trunc() lets you discard fractions, while keeping an appropriate type.

select now()::timestamp            as "now",
       to_char(now(),'HH:MI:SS')   as "to_char()",
       to_char(now(),'FMHH:MI:SS') as "fillmode", --trims one leading zero
       now()::time(0)              as "::time(0)",--rounds fractions
       date_trunc('seconds',now() )::time  as "date_trunc()";--discards fractions

/*          now             | to_char() | fillmode | ::time(0) | date_trunc()
----------------------------+-----------+----------+-----------+--------------
 2022-11-14 09:03:04.810214 | 09:03:04  | 9:03:04  | 09:03:05  | 09:03:04
(1 row)
*/
Zegarek
  • 6,424
  • 1
  • 13
  • 24