CODEWITHSUNDEEP
c#sqlpostgresqldatetimedate-formatting
Shesha
Shesha

Reputation: 2007

Convert date time into specified format in PostgreSQL query

For converting datetime into SQL Server CE, I am using below format

var date = "CONVERT(datetime,'" + 
((DateTime) DateTime.UtcNow).ToString("yyyyMMdd HH:mm:ss tt", new CultureInfo("en-us")) 
+ "',112)";

For Oracle:

var date = "TO_DATE('" 
+ ((DateTime)DateTime.UtcNow).ToString("MMddyyyy hh:mm:ss tt") 
+ "', 'mmddyyyy hh:mi:ss')";

And for MySQL:

var date = "STR_TO_DATE('" 
+ ((DateTime)DateTime.UtcNow).ToString("yyyyMMdd HH:mm:ss", new CultureInfo("en-us")) 
+ "','%Y%m%d %H:%i:%s')";

Can anyone tell me how to convert the same in PostgreSQL?

Note: I am using timestamp datatype

Upvotes: 4

Views: 14384

Answers (3)

Andrej Kmetík
Andrej Kmetík

Reputation: 186

If you want a very simple approach, you can also use this:

var date = $"'{new NpgsqlDateTime(DateTime.UtcNow)}'";

Edit: This has been deprecated since version 7.* of Npgsql. See this: https://github.com/npgsql/npgsql/issues/2009

Upvotes: 2

Mureinik
Mureinik

Reputation: 311843

You can use the to_char function to format a timestamp:

SELECT TO_CHAR(current_timestamp, 'MMDDYYYY HH24:MI:SS')

EDIT:
To answer the question in the comments, the same call could be applied in an insert statement:

INSERT INTO my_table
(formatted_timestamp_column, some_other_column)
VALUES (TO_CHAR(current_timestamp, 'MMDDYYYY HH24:MI:SS'), 'some_value')

Upvotes: 7

Laurenz Albe
Laurenz Albe

Reputation: 247235

With PostgreSQL, you can use the code from Oracle, just take to_timestamp instead of to_date.

The difference is that datein PostgreSQL is a real date and not a timestamp with second precision like it is in Oracle. So while you can use to_date as you do above, the result will not have any time information.

By, the way, your Oracle format is wrong. You probably want to use HH24 and not HH for the "hour" part.

Upvotes: 1

Related Questions