Share
Explore

Welcome

Achieving native timezone conversion within Coda.
If you are here, then you probably already know that
. Meaning that when any date or time is displayed, they are only displayed in the document time zone, with no ability to also display them in alternative time zones, such as the user's time zone.
However, I have found a way to work around it.

This
document
is configured to the timezone of
Africa/Johannesburg
which has an offset from UTC of
2
hours
, making the current time there now
9/18/2021 12:12:49 AM
,
which would be
9/17/2021 10:12:49 PM
in your timezone of
UTC
which has an offset from UTC of
0
hours
,
and
9/17/2021 10:12:49 PM
in UTC
.

You can even fiddle with the
When
column in this table to see more conversions:
Timezone Conversion Table
0
When (Document Time)
UTC
Your Time
1
April 3, 2020, 11:30 AM
April 3, 2020, 9:30 AM
April 3, 2020, 9:30 AM
2
April 3, 2020, 3:28 PM
April 3, 2020, 1:28 PM
April 3, 2020, 1:28 PM
3
April 3, 2020, 3:41 PM
April 3, 2020, 1:41 PM
April 3, 2020, 1:41 PM
There are no rows in this table
3
Count
Give it a go with a new row that will default to the current time
Add a new row


The Solution

Column Formulas

Set the
When (Document Time)
column to be formatted as a
Date and time
column, with the
Value for new rows
set to the formula:
Now()
Which results in the current time
9/18/2021 12:12:49 AM
being used as the default for new rows.

The timezone conversion formulas occur in the
UTC
and
Your Time
columns.

Set the
UTC
column to be formatted as a
Date and time
column, with the column formula (
Edit formula
) set to:
EpochToDate(
ToDays(thisRow.[When (Document Time)]) *
secondsInDay
+
utcDelta
)

Set the
Your Time
column to be formatted as a
Date and time
column, with the column formula (
Edit formula
) set to:
EpochToDate(
ToDays(thisRow.[When (Document Time)]) *
secondsInDay
+
localDelta
)


Formula Components

Set the following as
.

secondsInDay

ToSeconds(Duration(1))
Which equals
86400

secondsInHour

ToSeconds(Duration(0, 1))
Which equals
3600

documentOffsetInSeconds

DocumentTimezone().CurrentOffset *
secondsInHour
Which equals
7200

localOffsetInSeconds

CurrentTimezone().CurrentOffset *
secondsInHour
Which equals
0

currentSecondsSinceCodaEpoch

ToDays(Now()) *
secondsInDay
Which equals
3841085569.801

ToDays
is typically used with Coda Duration instances, however it turns out that when provided a Coda datetime instance, it returns a number which integer value represents the days since the Coda epoch and which decimal represents the portion of the day that has elapsed so far, giving us an accurate numeric value of the datetime.

This is the key to the ability to convert time zones in Coda, as all that is needed to convert time zones, is accurate numeric representations of dates such that math operations can be performed on them, which is fortunately what
ToDays(datetime)
gives us the ability to do.

We just need to figure out how to convert it from seconds since Coda epoch, to seconds since UNIX epoch, so it can be converted back into a datetime using
EpochToDate(secondsSinceUNIXEpoch)
. In short, we need a delta that we can apply to add or remove the distance between the two epochs. This delta is necessary, as converting
currentSecondsSinceCodaEpoch
directly to a Coda datetime instance with
EpochToDate(currentSecondsSinceCodaEpoch)
results in
9/20/2091 2:12:49 AM
which is in the future.

codaDelta

ToDays(EpochToDate(0)) *
secondsInDay
* -1
Which equals
-2209168800

EpochToDate(0)
gets the Coda datetime instance of the UNIX epoch, which is
1/1/1970 2:00 AM
when rendered with the current document timezone which is
Africa/Johannesburg
and is
2
hours ahead of UTC.

ToDays(EpochToDate(0))
converts the UNIX epoch datetime to the days since the Coda epoch which we then convert to seconds, which represents the distance in seconds from the Coda epoch to the UNIX epoch, which we then invert, such that it can be added to values that are from the Coda epoch (such as other
ToDays(datetime)
results, to make them instead the seconds from the UNIX epoch, to be converted back into datetimes with
EpochToDate(secondsSinceUNIXEpoch)
.

currentSecondsSinceUnixEpoch

currentSecondsSinceCodaEpoch
+
codaDelta
Which equals
1631916769.801

currentTime

EpochToDate(
currentSecondsSinceUnixEpoch
)
Which equals
9/18/2021 12:12:49 AM

utcDelta

epochDelta
-
documentOffsetInSeconds
Which equals
-2209176000

localDelta

utcDelta
+
localOffsetInSeconds
Which equals
-2209176000

utcTime

EpochToDate(
currentSecondsSinceCodaEpoch
+
utcDelta
)
Which equals
9/17/2021 10:12:49 PM

localTime

EpochToDate(
currentSecondsSinceCodaEpoch
+
localDelta
)
Which equals
9/17/2021 10:12:49 PM

Formula Reference

For easy lookup, here are the links to the relevant documentation for the different Coda base formulas that have been used
Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.