1

Based on a couple of assumptions here

1) I have about 6 clean formatted date columns imported from Excel that CartoDB converted into 5-digit strings. I cannot select 'date' in the column label.

2) Assumption two is that it's realistic and logical to change the dates so they're readable. (that is, convert them via SQL to eg YYDDMM).

I got help from HQ in the form of to convert the columns use: 

SELECT to_date(date_column::text, 'MDDYY') AS fixed_date

Since I don't know any other way to run SQL commands against my db, I'm trying to use the SQL API. After trying everything I could and from Google I just don't see why the above query would be useful. That is, was it perhaps offered as a per-map command I should put on relevant layers? So my approach is to try to run from REST but, shouldn't that then be a POST or UPDATE? Here's my latest:

https://myusername.cartodb.com/api/v2/sql?q=SELECT FROM myTableName to_date(date_column::text, 'MDDYY') AS fixed_date&myApiKey

At the end of the day I just want to change many date columns to readable dates that people can filter from and that anyone can read.

Improve this question

1 Answer 1

Reset to default
1

You can run any SQL query from the SQL tray on any layer or map visualization. In your case I'd do:

  • Create a new column of type date on your table
  • Run somethingl ike UPDATE mytable set mydatecolumn = to_date(stringcolumn,'MDDY')
  • Remove the string column when you are sure your date column is correct

Alternatively you can use the SQL API from any client (like cURL or the Chrome extension Postman) but then you need to pass your API KEY to run the UPDATE. More details here.

Improve this answer
5
  • Thanks for your help Jorge. When I try running that using the below statement I get the following response: myusername.cartodb.com/api/v2/sql?q=UPDATE myTable set newDateCol = to_date(oldNumberCol,'MDDYY') &api_key=mykey { "error": [ "function to_date(integer, unknown) does not exist" ], "hint": "No function matches the given name and argument types. You might need to add explicit type casts." } I've tried so many variants I'm getting lost.
    – civarchive
    May 31, 2016 at 22:00
  • What I hadn't realized is that if I just uploaded the data better prepared - eg the date cols formatted properly (right click col and format as date using Afrikaans eg 2016-05-30) and then I had to format the CSV properly (comma separated with all cell data quoted) using this Excel macro: support.microsoft.com/en-us/kb/291296. Then, after successfully importing in new Cartodb dataset I manually changed the columns datatype to 'date' and viola... all good to go. Thanks again Jorge.
    – civarchive
    May 31, 2016 at 23:49
  • Yep Postgres does a good job on casting to date from text if they are properly formatted. Glad to help.
    – Jorge Sanz
    Jun 1, 2016 at 14:17
  • If this helps anyone great. I got more feedback from Jorge and this is the final query that works for me: SELECT *, to_char(myDateColumn,'MM-DD-YY') as file_date FROM myTable WHERE myDateColumn <= '01-01-2014' and myDateColumn < '12-31-2014' NOTE: 'file_date' is a column now in my DB (auto-created? The infowindow is {{file_date}} and displays as FILE DATE: 03-03-14. That comma after the * was an elusive little bugger too. Thank Jorge.
    – civarchive
    Jun 7, 2016 at 16:12
  • Hey that file_date column is not persisted in your database, it's all on-the-fly calculation on your query. Glad to help. Cheers!
    – Jorge Sanz
    Jun 8, 2016 at 17:15

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.