date()

Converts a string to a timestamp type according to a specified format.

Syntax

Parameters

DATE_EXPR

The string or expression to convert to a timestamp type.

DATE_FMT

A date parsing format string as defined by Java's SimpleDateFormat class. Only a double-quoted string constant or a pre-bound query parameter is accepted, and the string must contain at least one date pattern letter. The following date pattern letters are supported.

Date pattern letters

Letter	Description	Example
G	Era designator	AD
y	Year in calendar	2025 (yyyy); 25 (yy)
M	Month in year	July (MMMM); Jul (MMM); 07 (MM), 7 (M)
w	Week in year	27 (27th week of the year)
W	Week in month	2 (second week of the month)
D	Day in year	10
d	Day in month	189
E	Day of week	Tuesday (EEEE); Tue (E, EE, EEE)
F	Day of week in month	2 (second occurrence of the weekday in the month)
u	Day number of week (1=Monday, …, 7=Sunday)	1
a	AM/PM marker	PM
H	Hour in day (0-23)	0
k	Hour in day (1-24)	24
K	Hour in AM/PM (0-11)	0
h	Hour in AM/PM (1-12)	12
m	Minute in hour	30
s	Second in minute	55
S	Millisecond	978
z	Time zone (general form)	Pacific Standard Time; PST
Z	Time zone (RFC 822 form)	-0800
X	Time zone (ISO 8601 form)	-08;-0800;08:00

LOCALE

A two- or three-character locale code as specified in ISO 639 (default: en). Refer to the ISO 639 code tables for locale codes.

Description

The date() function parses the string using the format specified in DATE_FMT and returns it as a timestamp type. If DATE_EXPR is null or an empty string, returns null. If parsing fails, also returns null. If an array or list is passed, the conversion is applied recursively to each element.

Logpresso does not strictly validate date value ranges. For example, month 13 is treated as January of the following year, and day 32 rolls over to the next month.

The date format is compiled once when the query is parsed and reused for every record. DATE_FMT therefore accepts only a string whose value is fixed at parse time: a double-quoted string constant ("yyyy-MM-dd"), or a query parameter bound in advance by the set command, a procedure argument, or a workflow input parameter and referenced with the $() function. A field reference, a function call such as concat(), a number, or an arithmetic expression fails with error code 90822 before the query runs. Writing the format without quotes (yyyy-MM-dd) or with single quotes ('yyyy-MM-dd') is parsed as a field reference and fails with the same 90822 error. This validation is available starting with 4.0.2609.0. The 5.0 series (through 5.0.2605.2) does not perform it.

A format string with no date pattern letter ("", "2013") cannot extract a date value from any input, so it is rejected with error code 90820. Characters enclosed in single quotes are treated as literal text and do not count as pattern letters. For example, "yyyy'th'" is valid because yyyy is a pattern letter, while "'year'" is not. This validation is also available starting with 4.0.2609.0. The 5.0 series (through 5.0.2605.2) does not perform it.

Error codes

Usage examples

1. Basic date string conversion

   json "{'val': '2024-06-10 00:30:55.978'}" | eval result = date(val, "yyyy-MM-dd HH:mm:ss.SSS")
   | # result: 2024-06-10 00:30:55+0900
   

2. ISO 8601 format conversion

   json "{'val': '2024-01-30T10:11:12.123Z'}" | eval result = date(val, "yyyy-MM-dd'T'HH:mm:ss.SSSX")
   | # result: 2024-01-30 19:11:12+0900
   

3. Date conversion using a Korean locale

   json "{'val': '6월 1 토요일 2024 12:34:56'}" | eval result = date(val, "MMM dd EEEE yyyy HH:mm:ss", "ko")
   | # result: 2024-06-01 12:34:56+0900
   

4. NULL input

   json "{'val': null}" | eval result = date(val, "yyyy-MM-dd")
   | # result: null
   

5. Specifying the date format with a query parameter

   set fmt = "yyyy-MM-dd HH:mm:ss"
   | json "{'val': '2013-06-10 00:30:55'}"
   | eval result = date(val, $("fmt"))
   | # result: 2013-06-10 00:30:55+0900
   

   A query parameter bound in advance by the set command has a fixed value at parse time, so you can use it for DATE_FMT.

6. Specifying a non-string-constant value for the format parameter — parse error

   json "{'val': '2013-06-10 00:30:55'}" | eval result = date(val, 20130610)
   

   Specifying a number (20130610), an arithmetic expression (1+1), a field reference, or an evaluated expression such as concat("yyyy-MM", "-dd") for DATE_FMT causes a parse error (error code 90822) before the query runs. The message reports the offending input, as in format parameter of date() should be constant string : 20130610, so you can see at a glance which argument to fix.

7. Specifying a format with no date pattern letter — parse error

   json "{'val': '2013-06-10 00:30:55'}" | eval result = date(val, "2013")
   

   "2013" has no date pattern letter and cannot extract a date value, so the query fails with a parse error (error code 90820) before it runs.

Change history