DateTime Conversion in PostgreSQL

Question

How can I convert 'dd-MM-yyyy HH:mm:ss.fff' to 'yyyy-MM-dd HH:MI:SS.MS' format so that I can store timestamp values in a PostgreSQL 8.0.2 timestamp column?

Ok, slowly: You have character fields containing the given format and want to convert them to a Timestamp? — Thilo
– Thilo, Commented Feb 23, 2013 at 7:54
More context please. Convert them within Postgres? In your programming language of choice (which?!) before inserting into Postgres? — deceze
– deceze ♦, Commented Feb 23, 2013 at 7:55
Thanks, i'm uploading application generated csv files to Amazon S3 and using copy command, the data from the files are inserted into PostgreSql tables. I've made changes to my app. to generate YYYY-MM-DD HH:MM:SS.fff format text, but the problem is i want to upload the existing files that are already generated with DD-MM-YYYY HH:MM:SS.fff format and don't want to regenerate them. — Pratik Borkar
– Pratik Borkar, Commented Feb 23, 2013 at 8:39
If PostgreSQL 8.0.2 isn't a typo, you have more serious problems than formatting timestamps. v8.0 has reached EOL in 2010. Consider upgrading to a more current version. — Erwin Brandstetter
– Erwin Brandstetter, Commented Feb 23, 2013 at 16:55

Daniel Vérité · Accepted Answer · 2013-02-23 12:20:59Z

1

Presumably you don't have to convert the format, just switch to european datestyle before issuing COPY.

The command is:

SET datestyle to 'DMY';

answered Feb 23, 2013 at 12:20

Daniel Vérité

62.2k16 gold badges133 silver badges159 bronze badges

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

Collectives™ on Stack Overflow

DateTime Conversion in PostgreSQL

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related