Omit 00 minutes from timestamp in PostgreSQL query

Question

I’m trying to form a query that will format times in a certain way.

What I have so far works in all cases except when the minutes portion of the timestamp are 00.

My aim is to omit minutes and the period between hours and minutes when the minutes are 00.

╔═══════════╗
║ StartTime ║
╠═══════════╣
║ 10:05 AM  ║
╟───────────╢
║ 10:25 AM  ║
╟───────────╢
║ 10:00 AM  ║
╚═══════════╝

e.g. 10:00 AM becomes 10 am, 10:05 AM becomes 10.00 am.

This is my query right now: to_char(timeslots.start_time, 'fmHH12.MI am') as start_time

I've gone through the PostgreSQL documentation on formatting but have had no luck.

Is this even possible?

Any help would be much appreciated!

Vao Tsun · Accepted Answer · 2018-03-14 12:30:17Z

2

simple replace would do the trick:

t=# with c(t) as (values(now()),(date_trunc('hour',now())))
select replace(to_char(t,'fmHH12:MI am'),':00 ',' ') from c;
 replace
----------
 12:29 pm
 12 pm
(2 rows)

answered Mar 14, 2018 at 12:30

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Omit 00 minutes from timestamp in PostgreSQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related