CODEWITHSUNDEEP
sqlpostgresqltimestampinsert-query
Shesha
Shesha

Reputation: 2007

How to add culture invariant info in timestamp in Postgresql?

How to add culture invariant info while adding timestamp in Postgresql database ?

Create table query:

CREATE TABLE Table1(Id SERIAL NOT NULL PRIMARY KEY,CreatedDate TIMESTAMP);

Insert query:

insert into Table1 (CreatedDate)
   values (convert(TIMESTAMP,'18-06-12 10:34:09 PM',112));

I am getting below error while executing query in postgresql database.

column "timestamp" does not exist

Is this right way to insert timestamp into column ?

Upvotes: 0

Views: 577

Answers (2)

D.Dimitrioglo
D.Dimitrioglo

Reputation: 3663

As I found out, you have to use ADD TIME ZONE like

(to_timestamp('18-06-12 10:34:09 PM', 'yy-MM-dd') AT TIME ZONE 'America/Los_Angeles')

P.S. America/Los_Angeles it is just an example

Upvotes: 1

Chris Travers
Chris Travers

Reputation: 26464

I think you are thinking about this backwards. You don't add culture invariant info to a timestamp. You format the timestamp using it (see to_char) or you use it to parse a timestamp (see the datestyle config option).

To insert a timestamp just insert it, and set your datestyle appropriately and then cast it to timestamp. So if this is always US, then:

but in your case, I think something is wrong in your example because:

SELECT '18-06-12 10:34:09 PM'::timestamp;

works because 2018 is a valid year but if I first SET DATESTYLE = 'US'; then I get an error because 18 is not a valid month.

Upvotes: 3

Related Questions