Hi everyone
I created a new glide big table. I have imported data into it via CSV.
The data contains a phone call log.
After the import I discovered that for date/time columns, glide automatically reduces 3 hours from the date and time value…
(Note: I unchecked the “respect time zone” we dont need it.)
So for example, in the CSV I have a date time of:
8/7/2024 19:01
glide big table shows it as:
8/7/2024 16:01
More over - On the layout view it shows it as 8/7/2024 23:01
When I switch the column type to text, the string shows the correct date and time… which is - 8/7/2024 19:01
So in total I have the date time showing in 3 different offsets…
Very confusing…
Does anyone know if that is a bug or a normal behavior?
When you import the CSV, Glide is applying your UTC offset to the date/time values.
What you need to do is reformat them as ISO8601 date/time strings with UTC offset included, like so:
2024-07-08T19:01:00+03:00
If you do that, they should import correctly.
2 Likes
Thanks Darren
Same goes for adding a row to a table via “Make”?
What do you mean by “your UTC offset”? is that dependent on the device I’m working on when importing?
Whats the best practice to a fail safe process where my data contains multiple timezones and also my staff works from multiple timezones?
Thanks 
Your UTC offset is determined by where you are physically located in the world. Yours appears to be UTC+3, which means you are 3 hours ahead of Greenwich Mean Time.
No, adding a row is fine. You can use the now value from Make. Just ensure that Make is configured to use the correct time zone.
Are you still talking here about importing via CSV, or adding rows via the App? You don’t have to do anything special when rows are added via the App, but if you have users in different timezones then you should have the “Respect time zone” option checked.
2 Likes
Our users dont add data to the system, the data comes from external sources and is for view only.
On a day to day basis it will come into the system via Make however
the nature of our process is that every once in a while we have to do a bulk upload of data via import, because of various data integrity reasons…
We prefer not to use the “Respect time zone”, we want to avoid confusion. there are account managers that are traveling all the time and we prefer them to see the absolute time and not the relative one…
We are working with bigquery as our DW but had to move to Glide big tables because of performance issues with Glide app…
Managing the data with BQ is easier, now that we moved to Glide big tables I need to find all sorts of workarounds for how to handle data integrity issues and bulk uploads…
Thanks for your help!
@Darren_Murphy
Circling back to this issue…
I now see an issue with Make integration - Add row to glide big tables.
In Make, I set the the correct timezone on a date/time field,(using formatDate formula on Make) and I can clearly see that Make sends the correct date/time.
When It gets to glide big tables its incorrect. no matter if I choose “respect time zone” or not.
Theoretically I could set a manual offset in Make to adjust for the correct time, but then I will need to adjust it twice a year on DST changes…
Hope you can shed some light…
What’s the time difference between the Make time and the Glide Table recorded time?
This is what Make says it inserted to Glide:
2:56 PM (Time zone is America/Los_Angeles)
This is the formula I use in Make - notice I set a time zone myself.
and also notice it says Time zone: America/New_york - Make support says that if I put a time zone in the formula myself it will use what I put (in this case America/Los_Angeles)
This is the time that I get in Glide - 7:56 PM
(note: “Respect time zone” is NOT checked)
So there is a 5 hours difference.
Which is probably done on Glide’s end because I can clearly see in Make that it puts the correct time in the field that is sent to Glide.
Thanks!
So your system_timezone variable says America/Los_Angeles right?
I don’t have a good reference for you on this. I have a use case that adds timestamps in different timezones, but to Glide Tables, not Glide Big Tables, several months ago.
I make a call to Worldtimeapi and treat the output as text. Then I substring it.
I wonder if the formatDate thingy brings a problem, but I lean more towards a GBT problem and you should talk to support about that.
1 Like
So I talked to support and sent a comprehensive screen recording.
This is what they responded:
"Glide will always see a datetime passed in from Make as UTC time. It does not accept timezone data.
- You should pass in the timezone in a separate column and then use a Javascript column to adjust for the timezone.
- Another option is to use make to adjust the date/time by factoring in the timezone. This way the data will come in as the real UTC time and then Glide you can adjust it based on the passed in timezone.
- A third option would be to not bring in the datetime as a date type and convert it to a string with the timezone name. Then create a text column in Glide and have Make write the data there as text.
@ThinhDinh @Darren_Murphy
Any thoughts/best practices from your experience with timezones on Glide (even regardless of Make)
Thanks for your help!
1 Like
Well, that response you got from Support is not what I would have expected, so I just did a bit of testing.
I created a new table and added some rows via the API, using curl from a terminal. Here are the results:
(NB: this was with a regular table. See further below for results with a Big Table.)
In the above:
- The first column is a date/time column with respect time zone enable
- The second column is a date/time column with respect time zone not enabled
- The third column is the value I passed to the API for the first two columns.
ie. each of my 3 add row mutations passed identical values to all 3 columns.
As a baseline, my local time zone is Asia/Singapore (UTC+8)
So, observations:
- The Respect Time Zone setting DOES make a difference when passing dates via the API
- Including a time zone offset also DOES make a difference when passing dates via the API
Looking at the results row by row:
- Row 1:
– The first column is correct. I passed 3pm and Glide correctly added my offset and so I see 11pm
– The second column is also correct.
- Row 2:
– The first column is correct. I passed 3pm, but I included a TZ offset, and so that was taken into account.
– The second column is also correct. It’s not respecting my time zone, so the value passed is shown as UTC
- Row 3:
– It looks like Glide messed up the first column. When passing a date only to a column with respect time zone enabled, I would expect the time to be interpreted as midnight, and then with my offset applied I would see Jan 22 at 8am (not 8pm)
– The second column looks correct. Date only passed, no time zone offset, respect time zone disabled, so treat as midnight UTC.
In summary, it appears that Glide does the right thing in most cases, with the exception being where you pass a date without a time to a column where respect time zone is enabled.
Update:
Immediately after posting this, I noticed that your original question was referring to Big Tables. My testing above was done with a regular table, so I repeated the test with a Big Table to see if there was any difference. And it turns out there is. Here are the results with a Big Table:
Observations:
- The first thing to notice is that the results in both columns are the same in all cases, so we can say the the respect timezone setting does indeed have no effect when adding dates to a Big Table.
- In rows 1 & 2, I am seeing the result as 11pm. This makes sense, because Glide would be treating the value I passed as UTC, and I see it with my offset applied (+8)
- Row 3 again looks dodgy. If I’ve passed a date without a time I would expect Glide to treat that as midnight UTC, and so again I should see it as 8am. But I see it as 8pm, which looks wrong.
4 Likes
Thanks so much for the comprehensive research!
Currently my solution is to manually create an offset, If I redo the timezone calculations I’ll have to figure out how to approach this on an existing GBT of apx 120K rows… I dont want to take any chances, I’ll have to create a staging env. to test it… will definitely take a while…
Thanks again!
I’d recommend exporting the data and load it up in a Google Spreadsheet. Then you can play around with array formulas to get it right, export to CSV, and import back into the Big Table.
I do this quite a bit, and it’s an approach I’ve had success with.
1 Like
Thanks for the leg work on this Darren. Very helpful
1 Like
I downloaded the glide big table.
Its 130,000 rows, 270 MB file.
Tried to import to g sheets but got an error - “Too large to import. Remove rows or columns and try again.”
I’ll try to split the file to smaller batches
So basically you suggest:
- export all the records
- Fix whatever needs to be fixed
- Download as csv
- Clear all the records from Glide big table
- Upload the fixed csv
Correct?
I believe this will also save me updates costs?
Thanks Darren
Yep, that’s essentially what I do 
2 Likes
