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?

  • Mixing time with months may not be helpful given that it would all wash out in torque. I can give it a shot though. – andrewxhill Jan 15 '14 at 20:02

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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