How UTC offsets affect datetime expressions

When ACL processes datetime expressions that include local time data with a UTC offset, the UTC offset is reconciled and the expression performs the calculation on the UTC equivalent of the local time. (UTC is Coordinated Universal Time, the time at zero degrees longitude.) Datetime functions also reconcile the UTC offset. In other words, if an expression or function encounters the local time 23:59:59-05:00, it actually performs the calculation on the UTC equivalent, which is 04:59:59. Dates in datetime data can also be affected. The UTC equivalent of 31 Dec 2014 23:59:59-05:00 is 01 Jan 2015 04:59:59.

By default, ACL displays local times with a UTC offset as their UTC equivalent, so you see the actual times that are being used in calculations. You also have the option of displaying the local time with the UTC offset. For more information about UTC, see Options dialog box: Date and Time tab.

If you are working with UTC-based data, you might think the results are incorrect if you do not account for this reconciling process. The reason that datetime expressions and functions work in this manner is that internally ACL stores local times with UTC offsets as their UTC equivalent.

The examples in Table 1 illustrate the effect of UTC offsets on datetime expressions. To assist with the illustration, a version of the expression using the UTC equivalent is also shown. This UTC-equivalent version is not visible in ACL when you process the expression.

Table 1. The effect of UTC offsets on datetime expressions

Datetime expression

Expression with UTC equivalent


`T235959` > `T230000`


T (True)

`T235959-0500` > `T230000`

`T045959` > `T230000`

F (False)

`20131231 235959` + 1


01 Jan 2014 23:59:59

`20131231 235959-0500` + 1

`20140101 045959` + 1

02 Jan 2014 04:59:59

CDOW(`20141231T235959`, 9)



CDOW(`20141231T235959-0500`, 9)

CDOW(`20150101T045959`, 9)








STOT(`T235959` - `T225959`)



STOT(`T235959-0500` - `T225959-0400`)

STOT(`T045959` - `T025959`)


Related concepts
Using datetimes in expressions
Related reference
Options dialog box: Date and Time tab

(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback