How UTC offsets affect datetime expressions

What is UTC?

UTC is Coordinated Universal Time, the time at zero degrees longitude, which is used as a global time standard to regulate time and time zones. UTC is closely associated with Greenwich Mean Time (GMT), and for many purposes the two can considered to be identical.

What is a UTC offset?

A UTC offset is the difference in hours and minutes between a particular time zone and UTC, the time at zero degrees longitude. For example, New York is UTC-05:00, which means it is five hours behind London, which is UTC±00:00.

In Analytics, time data with a UTC offset uses this format: hh:mm:ss±hh:mm . For example: 23:59:59-05:00

How Analytics processes a UTC offset

When Analytics 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. Datetime functions also reconcile the UTC offset. For example, 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 .

The reason that datetime expressions and functions work in this manner is that internally Analytics stores local times with UTC offsets as their UTC equivalent.

Dates can be affected

Reconciliation of the UTC offset can affect dates in datetime data if reconciliation moves the time forward or backward through the boundary of midnight. For example, the UTC equivalent of 31 Dec 2014 23:59:59-05:00 is 01 Jan 2015 04:59:59 .

UTC display option in Analytics

By default, Analytics displays local times with a UTC offset as their reconciled UTC equivalent, so you see the actual times that are being used in calculations. You also have the option of seeing the local time with the UTC offset displayed. For more information about UTC display, see Date and Time options.

Working with UTC-based data

If you are working with UTC-based data, you might think the results are incorrect if you do not account for the UTC reconciling process.

The examples in the table below 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 Analytics when you process the expression.

Datetime expression	Expression with UTC equivalent	Result
`T235959` > `T230000` `T235959-0500` > `T230000`	n/a `T045959` > `T230000`	T (True) F (False)
`20131231 235959` + 1 `20131231 235959-0500` + 1	n/a `20140101 045959` + 1	01 Jan 2014 23:59:59 02 Jan 2014 04:59:59
CDOW(`20141231T235959`, 9) CDOW(`20141231T235959-0500`, 9)	n/a CDOW(`20150101T045959`, 9)	Wednesday Thursday
MONTH(`20141231T235959`) MONTH(`20141231T235959-0500`)	n/a MONTH(`20150101T045959`)	12 1
STOT(`T235959` - `T225959`) STOT(`T235959-0500` - `T225959-0400`)	n/a STOT(`T045959` - `T025959`)	01:00:00 02:00:00