How to Format Timestamps

When the timestamp is formatted, what was defined replaces the date and time formats of the operating system (regional settings).

The correct syntax is TimestampFormat.

TimestampFormat	Output
YYYYMMDD	20241201
M/D/YY hh:mm:ss[.fff]	12/1/24 13:15:30
DD/MM/YYYY hh:mm:ss[.fff]	01/12/2024 13:15:30
DD/MM/YYYY hh:mm:ss[.fff] TT	01/12/2024 1:15:30 PM
YYYY-MM-DD hh:mm:ss[.fff] TT	2024-12-01 01:15:30 PM

Timestamp () - script and chart function

The Timestamp() function is used to format and display date and time values according to a specified format. It converts a numeric value representing a date and time into a textual date and time value.

It stores dates as the number of days since 30 December 1899.
For example:
45123 represents 1 December 2024.
.5 adds 12 hours (noon).

The correct syntax is Timestamp(number[, format])

Parameters

• expression:
  This is the numeric or date/time value that you want to format as a timestamp. Internally, Qlik Sense stores dates and times as numeric values (e.g., 45123.5 represents a specific date and time).

• format (optional):
  A string defining the desired display format for the timestamp. If not provided, the default format from the system's environment (TimestampFormat variable) is used.

  Common format elements:
  YYYY: Year (e.g., 2024)
  MM: Month (e.g., 11)
  DD: Day (e.g., 01)
  hh: Hours (e.g., 14 for 2 PM in 24-hour format)
  mm: Minutes (e.g., 30)
  ss: Seconds (e.g., 45)

• timezone (optional):
  Specifies the timezone for the timestamp. If not provided, the default system timezone is used.

Example 1: Default Format

Timestamp(45123.5)

• Assumes 45123.5 is a numeric representation of a date and time.
• Uses the system's default timestamp format to display the value (e.g. 2024-12-01 12:00:00)

Example 2: Custom Format

Timestamp(45123.5, 'DD/MM/YYYY hh:mm:ss')

• Converts 45123.5 into a timestamp and displays it as 01/12/2024 12:00:00.

Example 3: Including a Timezone

Timestamp(45123.5, 'YYYY-MM-DD hh:mm:ss', 'UTC')

• Displays the timestamp adjusted to UTC.

Timestamp# () - script and chart function

This function interprets a string as a timestamp based on a specified format and converts it into a numeric date-time value. This is useful when you have a string that represents a timestamp (e.g., '01/12/2024 12:00') and needs Qlik Sense to recognize and process it as a numeric date-time value.

The correct syntax is Timestamp#(text[, format])

Parameters

• text:

  • The text string you want to interpret as a timestamp.

  • This text string must represent a date and/or time in some format (e.g., '01/12/2024 12:00').

  • Qlik Sense will use the provided format to understand how to interpret the string

    Timestamp#('01/12/2024 12:00', 'DD/MM/YYYY hh:mm')

• format (optional):

  • A format string that specifies how the text string should be parsed.
  • It must match the structure of the expression exactly for Qlik Sense to interpret the timestamp correctly.
  • Common format elements:
    YYYY: Year (e.g. 2024)
    MM: Month (e.g. 11)
    DD: Day (e.g. 01)
    hh: Hours (e.g. 14 for 2 PM in 24-hour format)
    mm: Minutes (e.g. 30)
    ss: Seconds (e.g. 45)
  warning

  If the format is not provided, Qlik Sense will attempt to use the default TimestampFormat variable set in the environment, which might not match your string and could lead to incorrect results.

Example 1: Simple Conversion

Timestamp#('01/12/2024 12:30', 'DD/MM/YYYY hh:mm')

• Input: '01/12/2024 12:30'
• Format: 'DD/MM/YYYY hh:mm'
• Output: Numeric value, e.g., 45123.520833.

Example 2: Without Format (Defaults to Environment Settings)

Timestamp#('2024-12-01 08:30:00')

• Uses the system’s TimestampFormat setting to interpret the string.
• Risky if the input format does not match the system’s expected format.

Example 3: Adjusting for Timezone

Timestamp#('2024-12-01 18:00', 'YYYY-MM-DD hh:mm', 'UTC')

• Adjusts '2024-12-01 18:00' for UTC timezone.

How they work together

You often use these functions in sequence:

• Timestamp#(): To interpret raw text data as a numeric date-time value.
• Timestamp(): To format the interpreted numeric value for display.

Example: If you have a raw data field '01/12/2024 12:00' as text:

    Timestamp(Timestamp#('01/12/2024 12:00', 'DD/MM/YYYY hh:mm'), 'MM-DD-YYYY hh:mm')

• Timestamp#(): Interprets the text as 45123.5.
• Timestamp(): Formats it as 12-01-2024 12:00.

This separation of interpretation and formatting ensures flexibility and precision in handling date and time data.