1

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. Jan 15, 2014 at 20:02

1 Answer 1

2

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')
3
  • 1
    and if you did actually need the times, use to_timestamp with everything else the same.
    – Mike T
    Jan 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') ?
    – cholmes
    Jan 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?
    – cholmes
    Jan 16, 2014 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.