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?
1
-
Mixing time with months may not be helpful given that it would all wash out in torque. I can give it a shot though.– andrewxhillJan 15, 2014 at 20:02
Add a comment
|
1 Answer
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')
-
1and if you did actually need the times, use
to_timestampwith everything else the same.– Mike TJan 15, 2014 at 20:27 -
Looks like that forgets the day. Would day be done like:
UPDATE final_seadab_geo_only SET mission_date = to_date(hotel_day_of_mission || hotel_month_of_mission || hotel_year_of_mission || time_over_target, 'DDMonYYHH24MI')?– cholmesJan 16, 2014 at 15:39 -
That said it looks like my data is a bit too messy for this to work, it hits some that don't have a field for all of those. Is there some way to make it just do null if it hits a case it can't parse?– cholmesJan 16, 2014 at 15:41