How about ?
You could also send a formula to the sheet:
1 Like
Coercing clock instances into values compatible with Google Sheets should probably be natively supported by the component. @Susan_Lane can you take a look at this?
You're right. The component should do this. I'll put in an issue and see what I can do.
Watch out for time zone considerations.
They can be tricky, given the centralized nature of the sheet versus the localized nature of the mobile device.
1 Like
This is a good point. Full-featured datetime compatibility could get to be more work that we can commit to.
I'm only thinking about coercing the format. I don't think we can commit to tracking time zones. If it's relevant, the user will have to do it.
I guess spreadsheets don't generally know what time zone they're in, so that's not unreasonable.
I suppose it all depends if you want to align the time on the smartphone with the time on the google sheet.
Usually, one would post the smartphone time.
Just don't use google sheet date/ time formulas.
1 Like
Developers might upload all time in GMT, UTC or whatever to the SS and use their app to convert UTC to local time on a user's device.
1 Like
Regarding @SteveJG's point, most spreadsheet software uses the Microsoft format, which is a fractional form where the integer part is days since Dec 30, 1899 (ref Why 30th December 1899?) and the fractional part is multiplied by 86400 to get a particular second in a day (excepting leap seconds...). Therefore, I expect the "right" format will likely be this one support for Unix timestamps would be appreciated (but I'm not going to hold my breath).
Also, regarding timezones:
1 Like
As a stopgap, I suggest just formatting the datetime according to
which would be accepted by Google Sheets and leaving it to the app developer to follow the other recommendations at the bottom of Experiments with Logging DateTimes to Google Sheets
An AI2 app developer spanning time zones with their app would need to deal with their time zone issues on their own, while a new coder would still have a safety net under them if they send an Instant to Sheets.
I do think ISO 8601 tends to be a pretty good option (especially if you go the extra step to include the source timezone). In most cases, apps will likely not leave the original time zone as most of our users are students, but either UTC or ISO 8601 + timezone should cover most use cases, and for more advanced folks hopefully they're willing to take the dive into the time zone quagmire.
Whatever datetime format you use, it would be wise to see if Sheets can parse it using the =VALUE() function.
Here is the failure I got when i tried to send a 'z' format suffix:
(The VALUE() works when I don't send the EST.)
So according to the Google Sheets documentation, VALUE requires that the input be a datetime format that Google Sheets understands (a bit of a tautology, but anyway...).
According to this document, it looks like there isn't a way to assert time zones with the Google functionality, a bit surprising if true.
If this were a properly formatted ISO 8601 date-time, you'd want to be able to parse something like 2022-11-08T04:20:38-05:00
I tried the ISO 8601 X X X date format, from SimpleDateFormat (Java Platform SE 7 )
but it is not recognized by Sheets:
So far, there is no way to encode the time zone into the datetime that Sheets will recognize.
I don't know how particular sheets is on this front, but there shouldn't be a space between the time and the timezone.
Eh. I've tried a bunch of different things, but it looks like Sheets just will not accept a time zone per the ISO 8601 guidelines...
Relevant XKCD:
Removing the space did not improve Sheet's recognition capabilities.
In baseball, the solution would be to punt bunt:
What one uses for time depends on what one is gonna' do with it. Unix is fuzzy, UTC is simple.
The challenge here is that with something like Sheets as a backend, there is the potential that it is shown to someone outside of the context of the app. Neither Unix timestamps or UTC timestamps are particularly useful in that regard since you want to show the information as it is relevant to the viewer.
If it is 12:00 utc it means that the local time is + or - a number, usually an integer in arrears or ahead of the local time. All anyone needs is to know that UTC is what is posted to determine what is being talked about. UTC is a time standard that forms the base of time zones worldwide and is not used as a time zone in any country. Time zones are defined by their offsets from UTC.
UTC now (16:17 CST) is 22:17 GMT (London is 6 hours ahead of Dallas. or it is now 17:17 EST in Boston, and London is 5 hours ahead).It is 06:17 in Hong Kong (but be careful, it is already Wednesday morning but is still 22:17 in London and it is dark and still Tuesday!
In any case, please do not use AOE
mentioned as the Appathon time deadline. AOE is ambiguous and not a generally recognized time zone. 