Error with GUI mode and TIMESTAMP input – Invalid datetime string when using UTC +02:00

💬 App Building
, ,
1

Hi, I'm using Retool's GUI mode to insert data into BigQuery.

For the value field in my insert query, I was using the following expression:


{{ start_datetime.value ? start_datetime.value : { RT$BQ_TYPE: "TIMESTAMP", value: null } }}

This setup used to work without errors when the datetime was in UTC.
However, after switching the time zone of the Date Time input component to +02:00 , I started receiving this error:

Invalid datetime string "2025-07-08T01:27:43.190Z"

I'm using Retool's Date Time component as the input source.

Is there a recommended way to pass the timestamp in UTC+2 without causing this error?
Should I be formatting the value differently, or explicitly setting RT$BQ_TYPE?

Thanks in advance for any help!

3

Hi @mayaas, based on the error: Invalid datetime string "2025-07-08T01:27:43.190Z", it seems possible that BigQuery is trying to insert the data as a datetime type instead of a timestamp type. Datetime types do not accept time zones, so it doesn't like the Z in the string. This would line up with the fact that it started happening after including a timezone.

You can try modifying your ternary to explicitly define it as a timestamp, which accepts timezones:

{{ start_datetime.value ? { RT$BQ_TYPE: "TIMESTAMP", value: start_datetime.value } : { RT$BQ_TYPE: "TIMESTAMP", value: null } }}

However, google recommends storing all timestamps without timezones, and handling the timezone conversion at the application level.

1 Like