Military date/time to_char meridian indicator option

Question

Running PostgreSQL 7.4(Yep we're upgrading)

Field is data type: timestamp with time zone

Format is like this: 2011-06-30 19:18:07-04

Converting to this format:

to_char(datetime_field, 'MM-DD-YYYY HH12:MI:SS') AS "New Date"

Output is this:

06-30-2011 07:18:07

But I also need to add the meridian indicator.

Desired results:

// if AM
06-30-2011 07:18:07 AM

// if PM
06-30-2011 07:18:07 PM

Is there a option I can pass to get this?

So the example datetime I give

2011-06-30 19:18:07-04

should be this:

06-30-2011 07:18:07 PM

UPDATE:

Well I'm still looking for an option to pass but this is a workaround I did:

CASE WHEN date_part('HOUR', datetime_field) > 12
     THEN to_char(datetime_field, 'MM-DD-YYYY HH12:MI:SS PM')
     ELSE to_char(datetime_field, 'MM-DD-YYYY HH12:MI:SS AM')
END AS "New Date"

Answer 1

According to the documentation page (for v8.2), you can use any pattern from this list: AM, am, PM, pm, A.M., a.m., P.M., p.m.. The pattern chosen will define the style of the indicator in the output, but whether it will be ante meridiem or post meridiem, will entirely depend on the timestamp value.

So you don't need to employ CASE at all. Use whatever of the two:

• to_char(datetime_field, 'MM-DD-YYYY HH12:MI:SS AM') AS "New Date"

• to_char(datetime_field, 'MM-DD-YYYY HH12:MI:SS PM') AS "New Date"

Was that what you were asking about?