I received a CSV file that has year (just two digits, '70'), month (as 'JAN'), day and time (as '0310') as separate columns. Bringing in to CartoDB / PostGIS they all appear as strings. I'd like to convert them to a single column that is a real 'date' type so I can display with Torque. What's the best way to go about doing that?
I'd give this a shot.
First, create a new column, called mydates with type date. Then do the following SQL (replacing names of columns and your table where appropriate),
UPDATE mytable SET mydates = to_date(month_column || year_column || time_column, 'MonYYHH12MI')
That assumes you have a 12 hour clock. If it is 24 hour clock, do this,
UPDATE mytable SET mydates = to_date(month_column || year_column || time_column, 'MonYYHH24MI')