unixdt(X;Y)

Return a Unix-epoch time value given a string, date, or date+time value. (Available as of version 18.04)

Syntax

unixdt(X;Y)

Input

Argument Type Description
X decimal A scalar value or the name of a column containing date-related values.
Valid values can be of the form:
  • string
  • date+time
  • date
Y text An optional argument specifying which date mode to use if the date has an ambiguous format. For example, the string "1/2/3" can be interpreted as January 2, 2003 (using month/day/year format), or it can be interpreted as February 3, 2001 (using year/month/day format).

Valid values are:

  • 'mdymode' - Interpret ambiguities as month/day/year
  • 'dmymode' - Interpret ambiguities as day/month/year
  • 'ymdmode' - Interpret ambiguities as year/month/day

The default value is 'mdymode'.

You can also add an optional argument to tell todatetime how to handle invalid input.

Valid values are:
  • 'invalidna' - Return null on invalid input. This is the default value.
  • 'invaliderr' - Return an error on invalid input.
  • 'besteffort' - Allows a string that is technically not valid (such as containing invalid trailing characters) to be converted. If'besteffort' is unsuccessful, the function returns null.
  • 'allowyqm' - If specified, these functions will accept year (2021), quarter (20214), and month (202112) integer values. Without 'allowyqm', integer values are always construed as dates. This option will also allow strings that unambiguously specify a year ('2021'), a quarter ('2021Q4' or '4Q2021'), or a month ('12/2021'). Year values are treated as January 1 of the year, quarters as the first day of the quarter (such as April 1), and months as the first day of the month.

unixdt(X;Y) can take a variety of input values and uses internal logic to parse the dates. For example, the function assumes that a 4-digit number represents a year, and that a month name or abbreviation (such as 'Jan') is obviously a month. Any ambiguities are resolved using the setting of the Y argument.

Return Value

Returns a Unix-epoch time, which is the number of whole seconds since midnight, January 1, 1970 UTC, as a bigint (64-bit integer) value.

For more information about date formats, see Dates and time.

Example

The following example converts strings containing dates to a Unix-epoch time format. The Y argument is 'dmymode', so that any ambiguous dates are parsed as day/month/year.

<table cols="date_time">
"31 Oct 2017 12:15:10"
"February 3, 2016 04:45:01"
"1/2/5 13:26:05"
"3/12/15" 
</table>

<willbe name="unix_epoch" value="unixdt(date_time;'mdymode')" format="type:nocommas"/>