123
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 ?

Shesha
  • 1,857
  • 6
  • 21
  • 28
  • 1
    Maybe search for `CURRENT_TIMESTAMP`or even `NOW()`. there are at least precision arguments to the former. So `CURRENT_TIMESTAMP(3)`will yield 3 subecond digits (milli second resolution) – Dilettant Jul 07 '16 at 12:06
  • 1
    ... Formatting via SET DATESTYLE eg. at: [in postgres, can you set the default formatting for a timestamp, by session or globally?](http://stackoverflow.com/questions/8723574/in-postgres-can-you-set-the-default-formatting-for-a-timestamp-by-session-or-g) or in the official postgres docs – Dilettant Jul 07 '16 at 12:13
  • 3
    `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. –  Jul 07 '16 at 12:14
  • 1
    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 Jul 07 '16 at 12:16

3 Answers3

173

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

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • 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 – Shesha Jul 07 '16 at 12:29
  • @Shesha Don't use that in the `insert` query. In your `insert` use `current_timestamp` –  Jul 07 '16 at 12:32
14

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

Community
  • 1
  • 1
Krutika Patel
  • 312
  • 1
  • 17
3

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

c0delama
  • 663
  • 6
  • 9