2

I'm trying to convert a column in CartoDB from String to Date. My dates are the following format:

2014-01-02T00:02:00
2014-01-02T08:05:00
...

I've tried to append +00:00, so they looks like:

2014-01-02T00:02:00+00:00
2014-01-02T08:05:00+00:00
...

matching CartoDB's own dates. However, when converting from String to Date the time is lost and they become:

2014-01-02T00:00:00+00:00
2014-01-02T00:00:00+00:00
...

What to do?

6

CartoDB does use timestamp for the columns. So when a conversion doesn't work for you through the UI, you can use a bit of SQL to do it for you. In your case I would,

1) Create a new column called, my_time (or whatever you want)

2) Use a SQL statement to convert write the timestamp formatted strings to your new column

UPDATE table_name SET my_time = to_timestamp(my_strings, 'YYYY-MM-DD HH24:MI:SS')

Be careful here. There are a few things I assumed but am not certain about,

  1. Your hours are on a 24hour clock, change 24 to 12 if I was wrong
  2. Your format is Month-Day, not Day-Month
  3. You don't care about timezone (that is the +00:00 part)
1

I don't use CartoDB, but my intuition is that the Date type is using PostgreSQL's date type (PostgreSQL is the database backend for CartoDB) which has a resolution of 1 day. Instead you should be using the timestamp type (which may also be known as DateTime, YMMV).

You can convert from a string representation to a timestamp in PostgreSQL with the to_timestamp(text, text) function. Quite how this maps to CartoDB is left as an exercise.

  • CartoDB's own view on their date is: 2014-01-21T13:26:55+00:00. I'm going to see if the timestamp works. – user809829 Jan 21 '14 at 13:29

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.