TIME( ) function

Extracts the time from a specified time or datetime and returns it as a character string. Can also return the current operating system time.

Syntax

TIME(<time/datetime> <,format>)

Parameters

Name Type Description
time/datetime

optional

datetime

The field, expression, or literal value to extract the time from. If omitted, the current operating system time is returned in the format hh:mm:ss.
format

optional

 character

The format to apply to the output string, for example "hh:mm:ss". If omitted, the current Analytics time display format is used. You cannot specify format if you have omitted time/datetime.

Output

Character.

Examples

Basic examples

Literal input values

Returns "23:59:59" assuming an Analytics time display format of hh:mm:ss:

TIME(`20141231 235959`)

Returns "11:59 P":

TIME(`20141231 235959`, "hh:mm A")

Returns the current operating system time returned as a character string in hh:mm:ss format (24-hour clock):

TIME()

Field as input values

Returns a character string for each value in the Receipt_timestamp field, using the current Analytics time display format:

TIME(Receipt_timestamp)

Returns a character string for each value in the Receipt_timestamp field, using the specified time display format:

TIME(Receipt_timestamp,  "hh:mm:ss")

Advanced examples

Calculating the elapsed time for a command or a script to execute

Use the TIME( ) function to help calculate the amount of time a particular Analytics command, or an entire script, takes to execute.

Immediately before the command you want to time, or at the start of the script, specify this line to create a variable that stores the current operating system time:

ASSIGN Time_started = TIME()

Immediately after the command, or at the end of the script, specify the two lines below.

The first line creates a variable that stores the operating system time after the command or script completes. The second line calculates the difference between the finish and start times, and converts the result to an easily readable format.

Tip

You can double-click the CALCULATE log entry to see the elapsed time for the command or the script.

ASSIGN Time_finished = TIME()
CALCULATE STOT(CTOT(Time_finished) - CTOT(Time_started))

If the command or script will run over the boundary of midnight, use this second line instead:

CALCULATE `T000000` - (CTOT(Time_started) - CTOT(Time_finished))

Remarks

Output string length

The length of the output string is always 14 characters. If the specified output format, or the Analytics time display format, is less than 14 characters, the output string is padded with trailing blank spaces.

Parameter details

A field specified for time/datetime can use any time or datetime format, as long as the field definition correctly defines the format.

If you use format to control how the output string is displayed, you are restricted to the formats in the table below. You can use any combination of time and AM/PM formats. The AM/PM format is optional, and is placed last.

Specify format using single or double quotation marks. For example: "hh:mm:ss AM".

Time formats

AM/PM formats

Examples

hh:mm:ss

none

24-hour clock

"hh:mm:ss"

hhmmss

AM, or PM

12-hour clock

"hhmmss PM"

hh:mm

A, or P

12-hour clock

"hh:mm A"

hhmm

 

  

hh

 

 

Specifying a literal time or datetime value

When specifying a literal time or datetime value for time/datetime, you are restricted to the formats in the table below, and you must enclose the value in backquotes – for example, `20141231 235959`.

Do not use any separators such as slashes (/) or colons (:) between the individual components of dates or times.

  • Time values – you can use any of the time formats listed in the table below. You must use a separator before a standalone time value for the function to operate correctly. Valid separators are the letter 't', or the letter 'T'. 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 (-).

  • 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'.

    Example formats

    Example literal values

    thhmmss

    `t235959`

    Thhmm

    `T2359`

    YYYYMMDD hhmmss

    `20141231 235959`

    YYMMDDthhmm

    `141231t2359`

    YYYYMMDDThh

    `20141231T23`

    YYYYMMDD hhmmss+/-hhmm

    (UTC offset)

    `20141231 235959-0500`

    YYMMDD hhmm+/-hh

    (UTC offset)

    `141231 2359+01`

    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.

     

Related functions

If you need to return the current operating system time as a datetime value, use NOW( ) instead of TIME( ).

Other datetime conversion functions

Datetime to Character conversion

Function Description

DATE( )

Extracts the date from a specified date or datetime and returns it as a character string. Can also return the current operating system date.

DATETIME( )

Converts a datetime to a character string. Can also return the current operating system datetime.

Character or Numeric to Datetime conversion

Function Description

CTOD( )

Converts a character or numeric date value to a date. Can also extract the date from a character or numeric datetime value and return it as a date. Abbreviation for "Character to Date".

CTODT( )

Converts a character or numeric datetime value to a datetime. Abbreviation for "Character to Datetime".

CTOT( )

Converts a character or numeric time value to a time. Can also extract the time from a character or numeric datetime value and return it as a time. Abbreviation for "Character to Time".

Serial to Datetime conversion

Function Description

STOD( )

Converts a serial date – that is, a date expressed as an integer – to a date value. Abbreviation for "Serial to Date".

STODT( )

Converts a serial datetime – that is, a datetime expressed as an integer, and a fractional portion of 24 hours – to a datetime value. Abbreviation for "Serial to Datetime".

STOT( )

Converts a serial time – that is, a time expressed as a fractional portion of 24 hours, with 24 hours equaling 1 – to a time value. Abbreviation for "Serial to Time".

[ Back to top ]

ACL Scripting Guide 14.1