CartoDB Torque Time Stamp

Question

I have two spreadsheets that came from the same data export. One 25k points, one 55k points. Both have a time column including year, day, time down to the second. The first worked perfectly (25k points) - the visualization looks great. The second import works fine but when I change the field type from "string" to "date" it turns all of the times to 00:00:00 which makes the torque map just flash. I've reviewed the information at the following http://www.postgresql.org/docs/9.2/static/datatype-datetime.html

Any thoughts?

Answer 1

You can create an empty column of type date and then populate it by using a SQL query. With the Postgres function to_timestamp you'll be able to add the pattern in which your dates are written so that you'll avoid the issue you're having with the automatic conversion.

For example, if your dates are of the type:

2015-10-25 21:14:21

UPDATE tablename SET new_datecolumn_name = to_timestamp(old_datecolumn_name, 'YYYY-MM-DD HH24:MI:SS')

Or:

2015/10/25 21:14:21

UPDATE tablename SET new_datecolumn_name = to_timestamp(old_datecolumn_name, 'YYYY/MM/DD HH24:MI:SS')