Date fails in String -> Date in CartoDB

Question

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

2014年01月02日T00:02:00
2014年01月02日T08:05:00
...

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

2014年01月02日T00:02:00+00:00
2014年01月02日T08: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月02日T00:00:00+00:00
2014年01月02日T00:00:00+00:00
...

What to do?

Answer 1

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)

Answer 2

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.

• user809829: CartoDB's own view on their date is: 2014年01月21日T13:26:55+00:00. I'm going to see if the timestamp works.