How to insert current datetime in postgresql insert query [duplicate]

Question

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 ?

Maybe search for CURRENT_TIMESTAMPor even NOW(). there are at least precision arguments to the former. So CURRENT_TIMESTAMP(3)will yield 3 subecond digits (milli second resolution) — Dilettant
– Dilettant, Commented Jul 7, 2016 at 12:06
... Formatting via SET DATESTYLE eg. at: in postgres, can you set the default formatting for a timestamp, by session or globally? or in the official postgres docs — Dilettant
– Dilettant, Commented Jul 7, 2016 at 12:13
timestamp columns do not have "a format". Any formatting you see is applied by the SQL client you are using. Change the configuration of your SQL client or use a proper formatting function if you want a different display format. — user330315
– user330315, Commented Jul 7, 2016 at 12:14
Yes It ran on MySQL but now you want it to run in Postgresql so you need to look for the equivalent function in the Postgresql manual. — Clodoaldo Neto
– Clodoaldo Neto, Commented Jul 7, 2016 at 12:16

Bohemian · Accepted Answer · 2021-10-16 22:35:30Z

189

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

edited Oct 16, 2021 at 22:35

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

answered Jul 7, 2016 at 12:18

user330315

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

2 Comments

Shesha Over a year ago

If I use this, I am getting bellow error as my date is timestamp format. column "createddate" is of type timestamp without time zone but expression is of type text

user330315 Over a year ago

@Shesha Don't use that in the insert query. In your insert use current_timestamp

Community · Accepted Answer · 2017-05-23 11:47:19Z

15

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,.

edited May 23, 2017 at 11:47

CommunityBot

11 silver badge

answered Jul 7, 2016 at 12:14

Krutika Patel

3221 silver badge17 bronze badges

Comments

c0delama · Accepted Answer · 2016-07-07 12:12:58Z

3

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

edited Jul 7, 2016 at 12:12

user330315

answered Jul 7, 2016 at 12:11

c0delama

7736 silver badges9 bronze badges

Collectives™ on Stack Overflow

How to insert current datetime in postgresql insert query [duplicate]

3 Answers 3

2 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Linked

Related