Google Sheets IMPORTDATA date format

I’m using the IMPORTDATA function but dates are not appearing correctly in the Google Sheet.

For example, in the database query a date appears as 2019-09-11 00:00, but in the Google Sheet it is 43719

Is there a way to fix this?

Welcome to the forum. This isn’t an issue with Redash. It’s your Google Sheet. Change the format of the cell to “Date” and it will display as 2019-09-11.

Thanks, that works perfectly!

Hi. I tried to change the format of the cell to “Date”, and it shows “2073/09/01 18:52:36” from “63433.78653” when I want it to show as “2021-11-01 09:26:1635758796”. It works perfectly when I download the CSV file in redash and import it into google spreadsheet, but it does not work when I use =IMPORTDATA(). Could you give me some solutions please? Thanks,

It’s hard to say without further information. The question is what format does your database send to Redash? Does it send unix seconds? Or a formatted date?

Do you know how to see the format that Redash receives in your network inspector? That’s what we need to know.

It says type: “datetime”.

Right but what is the actual format of the data?

For example:

It is “2021-11-01T09:26:36.435”

Do you have any other specific details? I just used your exact data format and it works as expected in google sheets. I wrote this query:

-- postgres
SELECT '2021-11-01T09:26:36.435'::timestamp a_date

It returns a result exactly like yours (observe the datetime type):

and piped it into GoogleSheets with IMPORTDATA

It displays fine.

I used API key Results in CSV format in =IMPORTDATA()

Same here:

(I’ve regenerated the API key after this)

Are you certain your database table doesn’t include such a future date? That’s the only way I can reproduce what you describe.

This is what it looks like in redash table
I’m using it in Japan, Does timezone matter?

I don’t think Redash is the problem here.

• The dates show correctly in the Redash webapp
• They show correctly in our CSV file (which Google is reading).

So the problem is how your Google Sheet is interpreting the date. Potentially your geo location is affecting the way Google parses the dates. You could perhaps experiment with this. But unless you can point to a network response from Redash that shows the date sent incorrectly, I don’t think this is something we can fix with a patch on our end.

To absolutely rule out Redash you can open your network inspector while refreshing the google sheet. Then you can see the raw data that google is using to render your sheet. It will look something like this (notice the fetchData call)

Okay! Thanks a lot for your information

Hi. I contacted again since I want to ask if the problem might be in open source version of Redash, since what I told before happens only in open source version of Redash, and works fine in Redash service at app.redash.io. I did exactly same thing (import data from Redash to Google spreadsheet using =IMPORTDTA()) in those two (OSS Redash and Redash service at app.redash.io) and only OSS Redash did not work fine as for date format. I would very much appreciate your support.

Possibly. When you look at the network responses from OSS Redash versus hosted Redash, what’s the difference? Is the date presented in a different format?

Also, what version of OSS?

They are exactly the same. Both are type: “datetime” and “2021-11-01T09:26:36.435”.
Version is: 8.0.0+

I’m not able to reproduce. On a fresh instance of Redash V8 I followed these steps and I get the exact results as I did with app.redash.io.

If the network responses are the same from both (which they should be) then the problem must be either your database or Google sheets.

[edit] Here you can even test it yourself. Here’s my sample data: http://3.17.172.187/api/queries/2/results.csv?api_key=dWiFdkn5njgVTWo9XzKw4AipK160WknB34n0fXyB