Date/Time Format Codes

A list of valid components that can be used to specify the format of a date/time type: date, timestamp, or bigdatetime.

Date/time type formats must be specified with either the Event Stream Processor formatting codes, or a subset of timestamp conversion codes provided by the C++ strftime() function. The are a number of different valid codes, however, A valid date/time type specification can contain no more than one occurrence of a code specifying a particular time unit (for example, a code specifying the year).
Note: All designations of year, month, day, hour, minute, or second can also read a fewer number of digits than is specified by the code. For example, DD reads both two-digit and one-digit day entries.

Event Stream Processor Time Formatting Codes

Column Code Description Input Output
MM Month (01-12; JAN = 01). Y Y
YYYY Four-digit year. Y Y
YYY Last three digits of year. Y Y
YY Last two digits of year. Y Y
Y Last digit of year. Y Y
Q Quarter of year (1, 2, 3, 4; JAN-MAR = 1). N Y
MON Abbreviated name of month (JAN, FEB, ..., DEC). Y Y
MONTH Name of month, padded with blanks to nine characters (JANUARY, FEBRUARY, ..., DECEMBER). Y Y
RM Roman numeral month (1-XII; JAN = I). Y Y
WW Week of year (1-53), where week 1 starts on the first day of the year and continues to the seventh day of the year. N Y
W Week of month (1-5), where week 1 starts on the first day of the month and continues to the seventh day of the month. N Y
D Day of week (1-7; SUNDAY = 1). N Y
DD Day of month (1-31). Y Y
DDD Day of year (1-366). N Y
DAY Name of day (SUNDAY, MONDAY, ..., SATURDAY). Y Y
DY Abbreviated name of day (SUN, MON, ..., SAT). Y Y
HH Hour of day (1-12). Y Y
HH12 Hour of day (1-12). Y Y
HH24 Hour of day (0-23). Y Y
AM Meridian indicator (AM/PM). Y Y
PM Meridian indicator (AM/PM). Y Y
MI Minute (0-59). Y Y
SS Second (0-59). Y Y
SSSSS Seconds past midnight (0-86399). Y Y
SE Seconds since epoch (January 1, 1970 UTC). This format can only be used by itself, with the FF format, and/or with the time zone codes TZD, TZR, TZH and TZM. Y Y
MIC Microseconds since epoch (January 1, 1970 UTC). Y Y
FF Fractions of seconds (0-999999). When used in output, FF produces six digits for microseconds. FFFF produces twelve digits, repeating the six digits for microseconds twice. (In most circumstances, this is not the desired effect.) When used in input, FF collects all digits until a non-digit is detected, and then uses only the first six, discarding the rest. Y Y
FF[1-9] Fractions of seconds. For output only, produces the specified number of digits, rounding or padding with trailing zeros as needed. N Y
MS Milliseconds since epoch (January 1, 1970 UTC). When used for input, this format code can only be combined with FF (microseconds) and the time zone codes TZD, TZR, TZH, TZM. All other format code combinations generate errors. Furthermore, when MS is used with FF, the MS code must precede the FF code: for example, MS.FF. Y Y
FM Fill mode toggle: suppress zeros and blanks or not (default: not). Y Y
FX Exact mode toggle: match case and punctuations exactly (default: not). Y Y
RR Lets you store 20th century dates in the 21st century using only two digits. Y N
RRRR Round year. Accepts either four-digit or two-digit input. If two-digit, provides the same return as RR. Y N
TZD Abbreviated time zone designator such as PST. Y Y
TZH Time zone hour displacement. For example, -5 indicates a time zone five hours earlier than GMT. N Y
TZM Time zone hour and minute displacement. For example, -5:30 indicates a time zone that is five hours and 30 minutes earlier than GMT. N Y
TZR Time zone region name. For example, US/Pacific for PST. N Y

Strftime() Timestamp Conversion Codes

Instead of using Event Stream Processor time formatting codes, output timestamp formats can be specified using a subset of the C++ strftime() function codes. The following rules apply:
  • Any timestamp format specification that includes a percent sign (%) is considered a strftime() code.
  • Strings can only include one type of formatting codes: the Event Stream Processor formatting codes, or the strftime() codes.
  • Some strftime() codes are valid only on Microsoft Windows or only on UNIX-like operating systems. Different implementations of strftime() also include minor differences in code interpretation. To avoid errors, ensure that both the ESP Server and the ESP Studio are on the same platform, and are using compatible strftime() implementations. It is also essential to confirm that the provided codes meet the requirements for the platform.
  • All time zones for formats specified with strftime() are assumed to be the local time zone.
  • strftime() codes cannot be used to specify date/time type input, only date/time type output.
The Event Stream Processor supports the following strftime() codes:
Strftime() Code Description
%a Abbreviated weekday name; example: "Mon".
%A Full weekday name: for example "Monday".
%b Abbreviated month name: for example: "Feb".
%B Full month name: for example "February".
%c Full date and time string: the output format for this code differs, depending on whether Microsoft Windows or a UNIX-like operating system is being used. Microsoft Windows output example: 08/26/08 20:00:00 UNIX-like operating system output example: Tue Aug 26 20:00:00 2008
%d Day of the month, represented as a two-digit decimal integer with a value between 01 and 31.
%H Hour, represented as a two-digit decimal integer with a value between 00 and 23.
%I Hour, represented as a two-digit decimal integer with a value between 01 and 12.
%j Day of the year, represented as a three-digit decimal integer with a value between 001 and 366.
%m Month, represented as a two-digit decimal integer with a value between 01 and 12.
%M Minute, represented as a two-digit decimal integer with a value between 00 and 59.
%p Locale's equivalent of AM or PM.
%S Second, represented as a two-digit decimal integer with a value between 00 and 61.
%U Number of the week in the year, represented as a two-digit decimal integer with a value between 00 and 53, with Sunday considered the first day of the week.
%w Weekday number, represented as a one-digit decimal integer with a value between 0 and 6, with Sunday represented as 0.
%W Number of the week in the year, represented as a two-digit decimal integer with a value between 00 and 53, with Monday considered the first day of the week.
%x Full date string (no time): The output format for this code differs, depending on whether you are using Microsoft Windows or a UNIX-like operating system. Microsoft Windows output example: 08/26/08 UNIX-like operating system output example: Tue Aug 26 2008
%X Full time string (no date).
%y Year, without the century, represented as a two-digit decimal number with a value between 00 and 99.
%Y Year, with the century, represented as a four-digit decimal number.
%% Replaced by %.
Parent topic: Date and Time Programming