Skip to main content

DATETIME

This function parses the argument using the specified format if specified.

SYNTAX​

DATETIME ( arg [ , <format_modifier>,  format] )  

<format_modifier> ::=
    "FORMAT" 
    | "ZONE"

ARGUMENTS​

arg
A value to be converted to a timestamp. Valid arguments are numeric, date/time values, and strings. If a format is specified arg must be of type STRING.

format_modifier
Specifies how the format argument should be interpreted.

format
An optional string argument to specify how the arg should be interpreted.

Return Value​

The formatted time value of DATETIME data type.

Remarks​

  • When the arg is a numeric it is interpreted as the number of seconds since January 1, 1970 UTC.
  • If a string is provided and "FORMAT" is not specified, the default format is "2006-01-02 15:04:05"
  • If "ZONE" is specified, the format must be "UTC" or a location name corresponding to a file in the IANA Time Zone database, such as "America/Los_Angeles".

Examples​

A. Using string formatting​

This example interprets the provided string using the format provided.

SELECT DATETIME("2021-05-12", "FORMAT", "2006-01-02") AS formatted;

For a list of golang time formats see the time package.

Response:

FORMATTED [DATETIME]
2021-05-12 00:00:00

B. Using time zones​

Date & Time values in bSQL default to the UTC time zone. When a zone is specified, the zone information is added to the underlying time value.

SELECT DATETIME("2021-05-12 08:06:25", "ZONE", "America/New_York") AS formatted;

Response:

FORMATTED [DATETIME]
2021-05-12 08:06:25