How to insert current datetime in postgresql insert query

INSERT into Group (Name,CreatedDate) VALUES ('Test',UTC_TIMESTAMP(), 1);

This is the query I have used for mysql to insert current date time. When I am using this in postgresql, I am getting below error.

    HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
********** Error **********


ERROR: function utc_timestamp() does not exist
SQL state: 42883

I have tried like below using now(), however it is inserting like "2016-07-07 17:01:18.410677". I need to insert in 'yyyymmdd hh:mi:ss tt' format.

INSERT into Group (Name,CreatedDate) VALUES ('Test',UTC_TIMESTAMP(), 1);

How to insert current date time in insert query of postgresql in above format ?

312787 次浏览
小开

You can of course format the result of current_timestamp(). Please have a look at the various formatting functions in the official documentation.

小开

For current datetime, you can use now() function in postgresql insert query.

You can also refer following link.

insert statement in postgres for data type timestamp without time zone NOT NULL,.

小开

timestamp (or date or time columns) do NOT have "a format".

Any formatting you see is applied by the SQL client you are using.

To insert the current time use current_timestamp as documented in the manual:

INSERT into "Group" (name,createddate)
VALUES ('Test', current_timestamp);

To display that value in a different format change the configuration of your SQL client or format the value when SELECTing the data:

select name, to_char(createddate, 'yyyymmdd hh:mi:ss tt') as created_date
from "Group"

For psql (the default command line client) you can configure the display format through the configuration parameter DateStyle: https://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-DATESTYLE


提交答案