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.
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.Sign up to join this community
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:
SELECT string_column, 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 FROM your_table_name
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')