I am working on a mapping project which includes BCE dates, but I am having trouble getting CartoDB to understand them as dates. So far, the methods I have tried either give me a date in '69 or no date at all.

Any suggestions?

  • Could you share more of an example of how the data looks in your data table? – Andy Eschbacher Sep 11 '15 at 15:33
  • Sure! One way that the data has been inputted is with a format like -0050-11-02 (or 11/02/-50 etc.) to indicate November 2, 50 BCE. This results in "null" when the column is changed from string to date. Another way that the data has been inputted is with a format like -0049, which results in a '69 date when the column is changed from string to date. Alternatively, if I want to input the data manually with the column already set to date, I have no option to input anything earlier than 1900. – H.G. Sep 14 '15 at 18:24

I've found a very simple solution that I'm shocked I didn't think of before! By inputting the dates as YYYY-MM-DD BC/AD (e.g. 0050-11-02 BC) and then switching the column from string to date, I get exactly what I need.


You can convert your strings to dates using some of the date type formatting functions provided by PostGIS. For instance, you can run a query like this to see how the conversion does before overwriting values in your table:

  to_date(string_column,'YYYY-MM-DD') As date_formatted,
  to_char(to_date(string_column,'YYYY-MM-DD'),'YYYY-MM-DD AD') As show_era

You can read more about the string formatting in 'YYYY-MM-DD AD' here. The AD just tells the database function to output the era (could be BC or AD depending on the sign of the year), which isn't displayed normally and won't seem evident in the new date column, but should be there if your years are negative and you convert them as we've done above.

If you're happy with those results, you can create a custom query to include all the info from your current table, or you can create a new column for the date type and run the following query to populate the values based on the string column:

UPDATE your_table_name
SET new_date_column = to_date(string_column,'YYYY-MM-DD')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.