I'm trying to use historical dates to build a torque map on Cartodb.com, with data going back to 8000 BC. But the date format doesn't seem to convert anything bellow 0100 01 01 (1st Jan 100 AD), and I'm not even sure how to specify negative dates.
You can enter BCE dates or early CE dates with the following format: YYYY-MM-DD BC or YYYY-MM-DD AD. It's important to have all four digits there for the year (e.g. input 0050 for 50 BCE) and to have the dashes and the BC or AD addition (BCE and CE are not supported). Enter these as a string and then convert the column to a date format.
I've tried using something like:
UPDATE tablename set name = (SELECT TIMESTAMP WITH TIME ZONE 'epoch' + -62475116892 * INTERVAL '1 second')
but the negative date doesn't seem to work specially well, although for example Torque renders the map.
In the postgres docs it says that:
Date conventions before the 19th century make for interesting reading, but are not consistent enough to warrant coding into a date/time handler.