how to add AM or PM in PostgreSQL in date format itself?

4
votes

select to_date(to_char(sysdate,'yyyy-mm-dd hh12:mi:ss AM'), 'yyyy-MM-dd HH12:MI:SS AM') from dual;

This works fine in oracle but not in Postgres.

3
sqloraclepostgresql
What did you try so far? Oracle <> PostgreSQL, check the manual – Frank Heikens
I tried everything. The question is simple. Date with meridian indicator in timestamp without time zone datatype. There was no need to do a -1 on my question. – alpha_calling
The query makes no sense to begin with. You are converting sysdate to a formatted varchar just to convert that varchar back to a date which sysdate was to begin with. – a_horse_with_no_name

3 Answers

13
votes

Instead of sysdate you should use current_timestamp, or now():

SELECT TO_CHAR(current_timestamp, 'yyyy-mm-dd hh12:mi:ss AM')
1
votes

A table for add AM or PM format itself.

create table date_tbl ( id serial, Date_time varchar (30) default TO_CHAR(current_timestamp, 'yyyy-mm-dd hh:mi:ss AM') )

0
votes

Try the below function to accept time stamp with AM/PM over PostgreSQL

TO_CHAR(SYSDATE, 'MM/DD/YYYY HH12:MI:SS AM')