Import dates in BC into postgres

Question

I want to import the data of the following format (in a csv file) into postgres:

   ('string1', 'string2', 'string3', 1999-10-10,2000-10-10)

Here column1, column2, column3 are of type string and column4, column5 are of type date. My problem is, apart from normal dates like '1999-10-10'. I also want dates in "BC" to be imported into postgres, but I am not getting how to achieve the same. Can someone please help me with this?

Eg. I want dates of the following format to be imported into postgres:

  ('string1', 'string2', 'string3', 199-10-10 BC, 200-10-10 BC)

Is it possible to important csv fies with dates in BC as well as AD into postgres. If yes, can someone please help me with this as I am a complete novice at postgres?

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

From PostgreSQL docs:

Valid input for the time stamp types consists of the concatenation of a date and a time, followed by an optional time zone, followed by an optional AD or BC.

Date and time input is accepted in almost any reasonable format, including ISO 8601, SQL-compatible, traditional POSTGRES, and others.

And, most important in your case:

Remember that any date or time literal input needs to be enclosed in single quotes, like text strings.

So you need to quote datetime strings in the CSV, e.g. '199-10-10 BC'.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 26, 2013 at 12:25

Alex Shesterov

27.8k14 gold badges91 silver badges110 bronze badges

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

2 Comments

Jannat Arora Over a year ago

So is ('string1', 'string2', 'string3', '199-10-10 BC', '200-10-10 BC') a correct input format can I import it directly into postgres

Alex Shesterov Over a year ago

@JannatArora: exactly.

Collectives™ on Stack Overflow

Import dates in BC into postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related