Using datetimes in expressions

You can use expressions to perform calculations with dates, datetimes, and times:

  • Calculate elapsed days, elapsed days and time, or elapsed time

    For example, `20141231` - `20141130` returns 31, the number of days between the two dates.

  • Make positive or negative adjustments to dates, datetimes, or times

    For example, `20141231` - 15 returns 16 Dec 2014, the date 15 days earlier.

  • Compare dates, datetimes, or times

    For example, `20141231 183000` > `20141231 171500` returns T (True), because the first datetime is more recent than the second datetime.

Date and time functions

To help you work with datetime data, Analytics provides a number of date and time functions that perform a variety of useful tasks. You can use these functions when constructing datetime expressions.

The datetime functions, grouped by task, appear in the following table. You can see the same list of functions if you filter the Functions drop-down list in the Expression Builder by Date & Time.

Task performed

Functions

Returns the number of elapsed days (the age) between a date and a cutoff date, or the current date, or the number of elapsed days between any two dates.

AGE( )

Calculates a date, or a month end, a specified number of months before or after a date

GOMONTH( ), EOMONTH( )

Identifies the day of the week, or the month of the year, for a date

CDOW( ), CMOY( )

Returns a numeric value (1 to 7) representing the day of the week for a date

DOW( )

Extracts the date or the time from a datetime value

DATE( ), TIME( )

Extracts the day, month, year, hour, minutes, or seconds from a datetime value

DAY( ), MONTH( ), YEAR( ), HOUR( ), MINUTE( ), SECOND( )

Converts serial datetime values, or character or numeric datetime values, to regular datetime values with a Datetime data type

STOD( ), STODT( ), STOT( ), CTOD( ), CTODT( ), CTOT( )

Returns the current operating system date, datetime, or time

TODAY( ), DATETIME( ), NOW( )

An amount of time versus a point in time

As you work with datetimes in expressions, it is important to differentiate between amounts of time, and points in time, because the difference requires that you construct different types of expressions.

Times

The time value 08:30:00 could refer to an amount of time – 8 hours and 30 minutes – or a point in time – 08:30:00 AM in the morning.

Note

The Time Display Format (Tools > Options > Date and Time) in the first example is hh:mm:ss and in the second example is hh:mm:ss PM. Both examples involve serial datetime calculations, which are explained in subsequent sections.

Amount of time

If you subtract one time from another time the result is an elapsed time, which is an amount of time.

Returns 01:15:00 (1 hour, 15 minutes, 0 seconds):

STOT(`T083000` - `T071500`)

Point in time

If you add a number to a time, or subtract a number from a time, the result is a positive or negative adjustment that creates another point in time – either later or earlier than the initial time.

Returns 07:00:00 AM :

`T083000` - (1.00000000/24*1.5)

Dates

Amount of time

If you subtract one date from another date the result is the number of elapsed days, which is an amount of time.

Returns 31 , the number of days between the two dates:

`20141231` - `20141130`

Point in time

If you add a number to a date, or subtract a number from a date, the result is another point in time – either more recent or earlier than the initial date.

Returns 30 Nov 2014 , the date 31 days earlier:

`20141231` - 31

Valid and invalid datetime expressions

Datetime expressions encompass a number of possible combinations of datetime subtype (date, datetime, and time) and operator. Not all combinations are valid expressions. For example, you can subtract one date from another to find out the number of elapsed days, but you cannot add two dates because the operation is illogical. You can, however, add a number to a date to produce a subsequent date.

The following rules apply to datetime expressions:

  • Subtract or compare datetimes Any combination of date, datetime, or time values can be used in a subtraction operation or a comparison operation.

  • Add or subtract numbers and datetimes Whole numbers, mixed numbers, and fractional numbers can be subtracted from or added to date, datetime, or time values.

  • Add datetimes Date, datetime, or time values cannot be added to one another.

    If you need to add amounts of time, such as the hours worked in a week, you can use Analytics functions to extract the hour, minutes, and seconds portions of times as numeric values. You can then perform calculations on those numeric values. For more information, see Using functions to add amounts of time.

  • Compare datetimes and numbers Date, datetime, or time values cannot be compared to numbers.

The table below summarizes the combinations that are possible with datetime expressions and indicates whether each combination is valid or invalid – that is, whether it can be processed by Analytics.

Note

Even if an expression is valid, it may not always serve a useful analytical purpose. For example, Analytics will process the expression Finish_Date > Start_Time, but the result is always True (T) and comparing a date to a time serves no logical purpose.

 

Date value

Datetime value

Time value

Number

Date value

Valid:

Subtract

Compare

Valid:

Subtract

Compare

Valid:

Subtract

Compare

Valid:

Subtract

Add

Invalid:

Add

Invalid:

Add

Invalid:

Add

Invalid:

Compare

Datetime value

Valid:

Subtract

Compare

Valid:

Subtract

Compare

Valid:

Subtract

Compare

Valid:

Subtract

Add

Invalid:

Add

Invalid:

Add

Invalid:

Add

Invalid:

Compare

Time value

Valid:

Subtract

Compare

Valid:

Subtract

Compare

Valid:

Subtract

Compare

Valid:

Subtract

Add

Invalid:

Add

Invalid:

Add

Invalid:

Add

Invalid:

Compare

Data types returned by datetime expressions

The data type of the result returned by a datetime expression depends on the values and operator in the expression:

Datetime expression

Data type of result

Subtraction (datetime values only)

Any combination of date, datetime, or time values used in a subtraction operation

Numeric

A serial date, serial datetime, or serial time

For more information, see Serial datetimes.

Addition or subtraction (datetime values and numbers)

Whole number, mixed number, or fractional number subtracted from or added to a date, datetime, or time value

Datetime

A date, datetime, or time subtype of the Datetime data type

Comparison (datetime values only)

Any combination of date, datetime, or time values used in a comparison operation

Logical

T (True) or F (False)

Format of datetime literals

  • Datetime values you can use any combination of the date, separator, and time formats listed in the table below

    The date must precede the time, and you must use a separator between the two. Valid separators are a single blank space, the letter ‘t’, or the letter ‘T’.

  • Time values you must specify times using the 24-hour clock

    Offsets from Coordinated Universal Time (UTC) must be prefaced by a plus sign (+) or a minus sign (-).

    Example formats

    Example literal values

    YYYYMMDD

    `20141231`

    YYMMDD

    `141231`

    YYYYMMDD hhmmss

    `20141231 235959`

    YYMMDDthhmm

    `141231t2359`

    YYYYMMDDThh

    `20141231T23`

    YYYYMMDD hhmmss+/-hhmm

    (UTC offset)

    `20141231 235959-0500`

    YYMMDD hhmm+/-hh

    (UTC offset)

    `141231 2359+01`

    thhmmss

    `t235959`

    Thhmm

    `T2359`

    Note

    Do not use hh alone in the main time format with data that has a UTC offset. For example, avoid: hh+hhmm. Results can be unreliable.

     

Using functions to add amounts of time

You cannot directly add time values to one another in Analytics. However, you can use Analytics functions to extract the hour, minutes, and seconds portions of times as numeric values and then perform calculations on those numeric values.

The example of timesheet data below illustrates this approach using hours and minutes.

Several computed fields are required to produce the calculations:

Computed field name

Expression

Description

Elapsed

STOT(End_Time - Start_Time)

A subtraction operation that calculates the hours worked for the day. The STOT( ) function converts the results from serial time values to regular time values.

Hours

HOUR(Elapsed)

The hour portion extracted as a numeric value from the Elapsed value.

Minutes

MINUTE(Elapsed)

The minutes portion extracted as a numeric value from the Elapsed value.

(For display purposes. Not required for the calculation.)

Part Hours

MINUTE(Elapsed)/60.000

The minutes portion extracted as a numeric value from the Elapsed value, and calculated as a decimal fractional portion of 60 minutes.

Hours+Part Hours

Hours + Part_Hours

The numeric hours and part hours added together.

As a final step, you can total the Hours+Part Hours field to calculate the total hours for the week:

Making positive or negative adjustments to dates, datetimes, or times

You can make a positive or negative adjustment to date, datetime, or time values – for example, adding or subtracting 15 days, or adding or subtracting 3 hours.

Make a positive or negative adjustment to a date

Making a positive or negative adjustment to a date is straightforward. You add or subtract the required number of days to calculate one date based on another.

Returns 15 Jan 2015 :

`20141231` + 15

Returns 16 Dec 2014 :

`20141231` - 15

Make a positive or negative adjustment to a datetime or a time

Making a positive or negative adjustment to a datetime or a time value is somewhat more involved than making an adjustment to a date.

You cannot directly add time values to each other, or a time value to a datetime value. For example, if you try to make a positive adjustment of 3 hours using either of the following expressions, you get an error.

Returns Expression type mismatch error:

`t120000` + `t030000`

Returns Expression type mismatch error:

`20141231 235959` + `t030000`

Make a positive adjustment using the serial time equivalent

You can create a valid expression by adding the serial time equivalent of 3 hours (0.125), but manually creating such expressions can be awkward because it requires that you know what the serial time equivalent is.

Returns 15:00:00 :

`t120000` + 0.125

Returns 01 Jan 2015 02:59:59 :

`20141231 235959` + 0.125

Make a negative adjustment with a serial datetime or time result

Making a negative adjustment to a datetime or time value is easier to do, however the result is a serial datetime value or a serial time value that must be converted to a regular datetime or time value in order to be more human readable.

Returns 0.37500000000000 :

`t120000` - `t030000`

Returns 09:00:00 :

STOT(`t120000` - `t030000`)

Create a computed field to make positive or negative adjustments more easily

The following method allows you to make positive or negative adjustments to datetime or time values more easily:

  1. Create a computed field that calculates the serial time equivalent of the amount of time you want to add or subtract.

    Returns 0.10416668 , the serial time equivalent of 2-1/2 hours:

    (1.00000000/24*2.5)

    You need to specify ‘1’ with the multiple zeros in the decimal places to ensure Analytics does not round the result.

    You can change the number you multiple by to get an appropriate number of hours: (1.00000000/24*1) , (1.00000000/24*8) , (1.00000000/24*10.25) , and so on.

  2. In the same computed field, add or subtract the calculated serial time to or from the source time or datetime value.

    Returns values in the field + 2-1/2 hours:

    <time or datetime field> + (1.00000000/24*2.5)
  3. If you want to add or subtract both days and time to or from a datetime value, include an appropriate number of days in the calculation.

    Returns values in the field + 2 days and 2-1/2 hours:

    <datetime field> + 2 + (1.00000000/24*2.5)

Examples of datetime expressions

The tables below provide examples of valid datetime expressions:

Note

In a number of the examples, the results are returned as serial datetimes – that is, a date, datetime, or time value represented as an integer, or a decimal fractional portion of 24 hours.

You can use the STOD( ), STODT( ), and STOT( ) functions to convert serial datetime results into regular datetime values. For more information, see Serial datetimes.

Calculate elapsed days, days and time, or time

Expression

Result

`20141231` - `20141130`

31

The elapsed days between the two dates

Finish_Date - Start_Date

The elapsed days between Finish_Date and Start_Date values

`20141231 235959` - `20141130 114530`

31.51005787037037

The elapsed days and time between the two datetimes, with the time expressed as a serial time

STRING(INT(`20141231 235959` - `20141130 114530`),5) + " " + TIME(STOT(MOD(`20141231 235959` - `20141130 114530`, 1)))

31 12:14:29

The elapsed days and time between the two datetimes in the example above, expressed as days, hours, minutes, and seconds

Assumes a current Analytics time display format of hh:mm:ss
(`20141231 235959` - `20141130 114530`) * 24

756.24138888888888

The elapsed hours between the two datetimes in the example above, expressed as hours and a decimal fractional portion of an hour

Finish_Datetime - Start_Datetime

The elapsed days and time between Finish_Datetime and Start_Datetime values, with the time expressed as a serial time

STRING(INT(Finish_DateTime - Start_DateTime), 5) + " " + TIME(STOT(MOD(Finish_DateTime - Start_DateTime, 1)))

The elapsed days and time between Finish_Datetime and Start_Datetime values, expressed as days, hours, minutes, and seconds

`T235959` - `T114530`

0.51005787037037

The elapsed time between the two times, expressed as a serial time

STOT(0.51005787037037)

12:14:29

The serial time in the example above converted to a time value, using the current Analytics time display format

STOT(`T235959` - `T114530`)

12:14:29

The elapsed time between the two times, expressed as a time using the current Analytics time display format

Finish_Time - Start_Time

The elapsed times between Finish_Time and Start_Time values, expressed as a serial time

Make positive or negative adjustments to dates, datetimes, or times

Expression

Result

Due_date + 15

The values in the Due_date field incremented by 15 days

`20141231` - 15

16 Dec 2014

The date decremented by 15 days. Assumes a current Analytics date display format of DD MMM YYYY

`20141231 235959` + (1.00000000/24*1.5)

01 Jan 2015 01:29:59

The datetime plus 1.5 hours

`20141231 235959` - (1.00000000/24*1.5)

31 Dec 2014 22:29:59

The datetime minus 1.5 hours

STODT(`20141231 235959` - `T013000`)

31 Dec 2014 22:29:59

The datetime minus 1.5 hours

`20141231 235959` + 2 + (1.00000000/24*1.5)

03 Jan 2015 01:29:59

The datetime plus 2 days and 1.5 hours

`20141231 235959` - 2 - (1.00000000/24*1.5)

29 Dec 2014 22:29:59

The datetime minus 2 days and 1.5 hours

`t235959` + (1.00000000/24*1.5)

01:29:59

The time plus 1.5 hours

`T173000` - (1.00000000/24*1.5)

16:00:00

The time minus 1.5 hours

STOT(`T173000` - `T013000`)

16:00:00

The time minus 1.5 hours

STOT(STOT(`T173000` - `T013000`) - `T010000`)

15:00:00

The time minus 1.5 hours, minus another 1 hour

Compare dates, datetimes, or times

Expression

Result

`20141231` > `20141230`

T (True)

Due_date <= `20141231`

All values in the Due_date field on or before 31 Dec 2014

Payment_date > Due_date

All values in the Payment_date field past their due date

CTOD(DATE(Payment_timestamp, "YYYYMMDD"), "YYYYMMDD") > Due_date

All values in the Payment_timestamp field past their due date

To compare datetime and date values, the date is first extracted as a character value from the datetime values in the Payment_timestamp field, and then converted back to a date value to compare it with the due date.

To ensure date formats match, identical formats are specified for the DATE( ) format parameter (the output format) and the CTOD( ) format parameter (the input format).

Login_time > `t100000`

All values in the Login_time field later than 10:00:00

Datetime expressions that use conversion functions

Expression

Result

STOT(CTOT("t120000") - CTOT("t090000") )

03:00:00

The elapsed time between the two character time values

The character time values are first converted to regular time values so they can be used in a subtraction operation. The numeric serial time resulting from the subtraction operation is then converted to a regular time value.

CTOT(TIME(`20141231 125959`)) < `T235959`

T (True)

The time is first extracted as a character value from the datetime value, and then converted back to a time value to compare it with 23:59:59.