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