Date Functions
- concept
N1QL date functions return the system clock value or manipulate the datetime values, which are represented as a string or an integer. These functions are very useful for manipulating dates in datasets with various date formats and timezones.
Timezones
Datetime values are always tied to a specific timezone, either explicitly in the date value, or implicitly in the application. The date functions in N1QL therefore support multiple different timezones.
- UTC
-
UTC, The Coordinated Universal Time is the primary time standard by which the world regulates clocks and time. It is defined as the time at 0° longitude and is consistent, as it does not take into account daylight savings time. You can read further about UTC at https://www.timeanddate.com/time/aboututc.html.
All N1QL functions which accept a timezone as an argument also accept
UTC
. - IANA Timezones
-
Many applications operate across multiple different time zones and may not necessarily use
UTC
. Therefore, it is important for the database to be able to handle and manipulate dates in these time zones in a consistent manner. Many date functions take the time zone as an additional argument.Timezones are case sensitive, Europe/London
is not the same aseurope/london
.It is important to note that many time zones change their UTC offset based on daylight savings time, as a result the UTC offset of times may change based on the time of year. N1QL take this into account when converting dates.
Below are a few examples of commonly used timezones and their offsets:
Table 1. Common Timezones Timezone UTC Offset (without daylight savings time) UTC Offset (during daylight savings time) America/New_York
-05:00
-04:00
America/Tijuana
-08:00
-07:00
Europe/Paris
+01:00
+02:00
Europe/London
+00:00
+01:00
Asia/Tel_Aviv
+02:00
+03:00
Asia/Kolkata
+05:30
+05:30
- Local System Timezone
-
Many functions default to using the local timezone of the system, which will be one of the IANA timezones.
Date Formats
N1QL date functions accept dates in either Epoch/UNIX timestamp format or string date format.
N1QL is then able to represent the passed date as a standardized date object internally.
In general, functions whose name contains the word STR
are designed to use string formats while MILLIS
functions are designed to use Epoch/UNIX timestamps.
- Epoch/UNIX Timestamps
-
Epoch/UNIX time is the number of seconds (or milliseconds) that have elapsed since
1970-01-01T00:00:00.000Z
(Thursday, 1 January 1970 at midnight), not including leap seconds. This can be useful for numeric and timezone agnostic representations of dates. While Epoch/UNIX time can be represented in either seconds or milliseconds, all N1QL date functions specifically treat Epoch/UNIX timestamps as milliseconds. For example, the date2017-01-31T10:02:07Z
would equate to an Epoch/UNIX timestamp of 1485856927000. - Date String Formats
-
In many cases, dates are not stored as Epoch/UNIX timestamp but instead as more human-readable formats, such as
2006-01-02T15:04:05.567+08:00
. Therefore, N1QL also provides convenience methods to allow you to manipulate and convert dates in string format. All date formats follow the ISO-8601 standard.The supported string formats are composed of the following components:
Table 2. Date String Components Component Code Example Year
YYYY
2006
Month (of the year)
MM
01
Day (of the month)
DD
02
Hour (of the day)
hh
15
Minute (of the hour)
mm
04
Second (of the minute)
ss
05
Millisecond (of the second)
s
567
Time Zone (as UTC offset)
TZD
-08:00
A UTC offset of 0 (+00:00) can just be specified as Z
The following table shows all of the accepted string date formats for N1QL date functions, using the date components specified above. In cases where the timezone is not specified, the local system time is assumed:
Table 3. Date String Examples Format Example YYYY-MM-DDThh:mm:ss.sTZD
2006-01-02T15:04:05.999+00:00 or 2006-01-02T15:04:05.999Z
YYYY-MM-DDThh:mm:ssTZD
2006-01-02T15:04:05+00:00 or 2006-01-02T15:04:05Z
YYYY-MM-DDThh:mm:ss.s
2006-01-02T15:04:05.999
YYYY-MM-DDThh:mm:ss
2006-01-02T15:04:05
YYYY-MM-DD hh:mm:ss.sTZD
2006-01-02 15:04:05.999+00:00 or 2006-01-02 15:04:05.999Z
YYYY-MM-DD hh:mm:ssTZD
2006-01-02 15:04:05+00:00 or 2006-01-02 15:04:05Z
YYYY-MM-DD hh:mm:ss.s
2006-01-02 15:04:05.999
YYYY-MM-DD hh:mm:ss
2006-01-02 15:04:05
YYYY-MM-DD
2006-01-02
hh:mm:ss.sTZD
15:04:05.999+00:00 or 15:04:05.999Z
hh:mm:ssTZD
15:04:05+00:00 or 15:04:05Z
hh:mm:ss.s
15:04:05.999
hh:mm:ss
15:04:05
Currently N1QL only accepts numeric format strings with valid values for each component. For example, you should pass something like
"1111-11-11"
as the date format rather than"YYYY-MM-DD"
, this has the same effect as passing in any other valid date in the same format (such as"2017-01-31"
).If the date string does not explicitly declare the value of a component, then a value of 0 is assumed. For example
2016-02-07
is equivalent to2016-02-07T00:00:00
. This is with the exception of the time zone, which if unspecified in the date string will default to the local system time zone.N1QL is very specific as to what it accepts as a valid date format, below are a few examples:
-
"01:01:01"
- Valid -
"hh:mm:ss"
- Invalid -
"01:01:01.111"
- Valid -
"01:01.111"
- Invalid -
"2017-01-31"
- Valid -
"2017-01-86"
- Invalid
-
Manipulating Date Components
Dates are composed of multiple different components such as the day, year, month etc.
It is important for applications to be able to manipulate particular components of a date.
Functions such as DATE_ADD_STR() accept a part
argument, which is the component to adjust.
Below is a list of accepted parts, these are expressed as strings and are not case-sensitive:
-
millennium
-
century
-
decade
-
year
-
quarter
-
month
-
week
-
day
-
hour
-
minute
-
second
-
millisecond
Extracting Date Components
It is important for applications to be able to extract the specific component of the timestamps, such as day, year, month, hours, minutes, or seconds, so that these can be used in N1QL queries.
The following are the supported date parts that can be passed to the date extraction functions.
These date parts are expressed as strings and are not case-sensitive, so year
is regarded the same as YeAr
.
For all examples, the date being used is 2006-01-02T15:04:05.999Z
Component | Description | Lower Bound | Upper Bound | Example |
---|---|---|---|---|
millennium |
The millennium (1000 year period), which begins at 0 BCE. |
- |
- |
3 |
century |
The century (100 year period), which begins at 0 BCE. |
- |
- |
21 |
decade |
The decade (10 year period).
This is calculated as |
- |
- |
200 |
year |
The Gregorian year, which begins at 0 BCE |
- |
- |
2006 |
iso_year |
The ISO-8601 week number of the year.
Each ISO-8601 year begins with the Monday of the week containing the 4th of January, so in early January and late December the ISO year may differ from the Gregorian year.
Should be used in conjunction with |
- |
- |
2006 |
quarter |
The number of the quarter (3 month period) of the year. January-March (inclusive) is 1 while October-December (inclusive) is 4. |
1 |
4 |
1 |
month |
The number of the month of the year. January is 1 and December is 12. |
1 |
12 |
1 |
week |
The number of the week of the year. This is the ceiling value of the day of the year divided by 7. |
1 |
53 |
1 |
iso_week |
The number of the week of the year, based on the ISO definition.
ISO weeks start on Mondays and the first week of a year contains January 4 of that year.
In other words, the first Thursday of a year will always be in week 1 of that year.
This results in some different results between week and For example the |
1 |
53 |
1 |
day |
The day of the month. |
1 |
31 |
2 |
day_of_year or doy |
The day of the year. |
1 |
366 |
2 |
day_of_week or dow |
The day of the week. |
0 |
6 |
1 |
hour |
The hour of the day. |
0 |
23 |
5 |
minute |
The minute of the hour. |
0 |
59 |
4 |
second |
The second of the minute. |
0 |
59 |
5 |
millisecond |
The millisecond of the second. |
0 |
999 |
999 |
timezone |
The offset from UTC in seconds. |
-43200 |
43200 |
0 |
timezone_hour |
The hour component of the offset from UTC. |
-12 |
12 |
0 |
timezone_minute |
The minute component of the offset from UTC. |
-59 |
59 |
0 |
Date Functions
Below is a list of all date functions that N1QL provides.
Many date functions use the local system clock value and timezone of the query node to generate results. Therefore, if all nodes running the query service do not have their time appropriately synchronized then you may experience inconsistent behavior. It is recommended that all Couchbase Server nodes have their time synchronized via NTP. |
If any arguments to any of the following functions are MISSING then the result is also MISSING (i.e.
no result is returned).
Similarly, if any of the arguments are NULL then NULL is returned.
|
CLOCK_LOCAL([fmt])
- Description
-
The current time (at function evaluation time) of the machine that the query service is running on, in the specified string format.
- Arguments
-
- fmt
-
A string, or any valid expression which evaluates to a string, representing a supported date format to output the result as.
Optional argument, if no format or an incorrect format is specified, then this defaults to the combined full date and time.
- Return Value
-
A date string in the format specified representing the local system time.
- Limitations
-
CLOCK_LOCAL()
cannot be used as part of an index definition, this includes the indexed fields and theWHERE
clause of the index.If this function is called multiple times within the same query then the values returned may differ, particularly if the query takes a long time to run. To avoid inconsistencies between multiple calls to
CLOCK_LOCAL()
within a single query, use NOW_LOCAL() instead. - Examples
-
SELECT CLOCK_LOCAL() as full_date, CLOCK_LOCAL('invalid date') as invalid_date, CLOCK_LOCAL('1111-11-11') as short_date;
Results:
[ { "full_date": "2018-01-23T13:57:29.847-08:00", "invalid_date": "2018-01-23T13:57:29.847-08:00", "short_date": "2018-01-23" } ]
CLOCK_MILLIS()
- Description
-
The current time as an Epoch/UNIX timestamp. Its fractional part represents nanoseconds, but the additional precision beyond milliseconds may not be consistent or guaranteed on all platforms.
- Arguments
-
This function accepts no arguments.
- Return Value
-
A single float value (with 3 decimal places) representing the system time as Epoch/UNIX time.
- Limitations
-
CLOCK_MILLIS()
cannot be used as part of an index definition, this includes the indexed fields and theWHERE
clause of the index.If this function is called multiple times within the same query then the values returned may differ, particularly if the query takes a long time to run. To avoid inconsistencies between multiple calls to
CLOCK_MILLIS()
within a single query, use NOW_MILLIS() instead. - Examples
-
SELECT CLOCK_MILLIS() AS CurrentTime;
Results:
[ { "CurrentTime": 1516744600430.677 } ]
CLOCK_STR([fmt])
- Description
-
The current time (at function evaluation time) of the machine that the query service is running on, in the specified string format.
- Arguments
-
- fmt
-
A string, or any valid expression which evaluates to a string, representing a supported date format to output the result as. .
Optional argument, if no format or an incorrect format is specified, then this defaults to the combined full date and time.
- Return Value
-
A date string in the format specified representing the system time.
- Limitations
-
CLOCK_STR()
cannot be used as part of an index definition, this includes the indexed fields and theWHERE
clause of the index.If this function is called multiple times within the same query then the values returned may differ, particularly if the query takes a long time to run. To avoid inconsistencies between multiple calls to
CLOCK_STR()
within a single query, use NOW_STR() instead. - Examples
-
SELECT CLOCK_STR() as full_date, CLOCK_STR('invalid date') as invalid_date, CLOCK_STR('1111-11-11') as short_date;
Results:
[ { "full_date": "2018-01-23T13:55:10.798-08:00", "invalid_date": "2018-01-23T13:55:10.798-08:00", "short_date": "2018-01-23" } ]
CLOCK_TZ(tz [, fmt])
- Description
-
The current time (at function evaluation time) in the timezone given by the timezone argument passed to the function. This time is the local system time converted to the specified timezone.
- Arguments
-
- tz
-
A string, or any valid expression which evaluates to a string, representing the timezone to convert the local time to.
If this argument is not a valid timezone then
null
is returned as the result. - fmt
-
A string, or any valid expression which evaluates to a string, representing a supported date format to output the result as.
Optional argument, if no format or an incorrect format is specified, then this defaults to the combined full date and time.
- Return Value
-
An date string in the format specified representing the system time in the specified timezone.
- Limitations
-
As this function converts the local time, it may not accurately represent the true time in that timezone.
CLOCK_TZ()
cannot be used as part of an index definition, this includes the indexed fields and theWHERE
clause of the index.If this function is called multiple times within the same query then the values returned may differ, particularly if the query takes a long time to run. To avoid inconsistencies between multiple calls to
CLOCK_TZ()
within a single query, use NOW_TZ() instead. - Examples
-
SELECT CLOCK_TZ('UTC') as UTC_full_date, CLOCK_TZ('UTC', '1111-11-11') as UTC_short_date, CLOCK_TZ('invalid timezone') as invalid_timezone, CLOCK_TZ('US/Eastern') as us_east, CLOCK_TZ('US/Pacific') as us_west;
Results:
[ { "UTC_full_date": "2018-01-23T21:54:37.178Z", "UTC_short_date": "2018-01-23", "invalid_timezone": null, "us_east": "2018-01-23T16:54:37.18-05:00", "us_west": "2018-01-23T13:54:37.181-08:00" } ]
CLOCK_UTC([fmt])
- Description
-
The current time in UTC. This time is the local system time converted to UTC. This function is provided for convenience and is the same as
CLOCK_TZ('UTC')
. - Arguments
-
- fmt
-
A string, or any valid expression which evaluates to a string, representing a supported date format to output the result as.
Optional argument, if no format or an incorrect format is specified, then this defaults to the combined full date and time.
- Return Value
-
An date string in the format specified representing the system time in UTC.
- Limitations
-
As this function converts the local time, it may not accurately represent the true time in UTC.
CLOCK_UTC()
cannot be used as part of an index definition, this includes the indexed fields and theWHERE
clause of the index.If this function is called multiple times within the same query then the values returned may differ, particularly if the query takes a long time to run. To avoid inconsistencies between multiple calls to
CLOCK_UTC()
within a single query, use NOW_UTC() instead. - Examples
-
SELECT CLOCK_UTC() as full_date, CLOCK_UTC('1111-11-11') as short_date;
Results:
[ { "full_date": "2018-01-23T21:54:03.593Z", "short_date": "2018-01-23" } ]
DATE_ADD_MILLIS(date1, n, part)
- Description
-
Performs date arithmetic on a particular component of an Epoch/UNIX timestamp value. This calculation is specified by the arguments
n
andpart
.For example, a value of 3 for
n
and a value ofday
forpart
would add 3 days to the date specified bydate1
. - Arguments
-
- date1
-
An integer, or any valid expression which evaluates to an integer, representing an Epoch/UNIX timestamp in milliseconds.
If this argument is not an integer then
null
is returned. - n
-
The value to increment the date component by. This value must be an integer, or any valid expression which evaluates to an integer, and may be negative to perform date subtraction.
If a non-integer is passed to the function then
null
is returned. - part
-
A string, or any valid expression which evaluates to a string, representing the component of the date to increment.
If an invalid part is passed to the function then
null
is returned.
- Return Value
-
An integer, representing the result of the calculation as an Epoch/UNIX timestamp in milliseconds.
- Examples
-
SELECT DATE_ADD_MILLIS(1463284740000, 3, 'day') as add_3_days, DATE_ADD_MILLIS(1463284740000, 3, 'year') as add_3_years, DATE_ADD_MILLIS(1463284740000, -3, 'day') as sub_3_days, DATE_ADD_MILLIS(1463284740000, -3, 'year') as sub_3_years;
Results:
[ { "add_3_days": 1463543940000, "add_3_years": 1557892740000, "sub_3_days": 1463025540000, "sub_3_years": 1368590340000 } ]
DATE_ADD_STR(date1, n, part)
- Description
-
Performs date arithmetic on a date string. This calculation is specified by the arguments
n
andpart
. For example a value of 3 forn
and a value ofday
forpart
would add 3 days to the date specified bydate1
. - Arguments
-
- date1
-
A string, or any valid expression which evaluates to a string, representing the date in a supported date format.
- n
-
The value to increment the date component by. This value must be an integer, or any valid expression which evaluates to an integer, and may be negative to perform date subtraction.
If a non-integer is passed to the function then
null
is returned. - part
-
A string, or any valid expression which evaluates to a string, representing the component of the date to increment.
If an invalid part is passed to the function then
null
is returned.
- Return Value
-
An integer representing the result of the calculation as an Epoch/UNIX timestamp in milliseconds.
- Examples
-
SELECT DATE_ADD_STR('2016-05-15 03:59:00Z', 3, 'day') as add_3_days, DATE_ADD_STR('2016-05-15 03:59:00Z', 3, 'year') as add_3_years, DATE_ADD_STR('2016-05-15 03:59:00Z', -3, 'day') as sub_3_days, DATE_ADD_STR('2016-05-15 03:59:00Z', -3, 'year') as sub_3_years;
Results:
[ { "add_3_days": "2016-05-18T03:59:00Z", "add_3_years": "2019-05-15T03:59:00Z", "sub_3_days": "2016-05-12T03:59:00Z", "sub_3_years": "2013-05-15T03:59:00Z" } ]
DATE_DIFF_MILLIS(date1, date2, part)
- Description
-
Finds the elapsed time between two Epoch/UNIX timestamps. This elapsed time is measured from the date specified by
date2
to the date specified bydate1
. Ifdate1
is greater thandate2
, then the value returned will be positive, otherwise the value returned will be negative. - Arguments
-
- date1
-
An integer, or any valid expression which evaluates to an integer, representing a Epoch/UNIX timestamp in milliseconds. This is the value that is subtracted from
date1
.If this argument is not an integer, then
null
is returned. - date2
-
An integer, or any valid expression which evaluates to an integer, representing a Epoch/UNIX timestamp in milliseconds.
This is the value that is subtracted from
date1
.If this argument is not an integer, then
null
is returned. - part
-
A string, or any valid expression which evaluates to a string, representing the component of the date to increment.
If an invalid part is passed to the function, then
null
is returned.
- Return Value
-
An integer representing the elapsed time (based on the specified
part
) between both dates. - Examples
-
SELECT DATE_DIFF_MILLIS(1463543940000, 1463284740000, 'day') as add_3_days, DATE_DIFF_MILLIS(1557892740000, 1463284740000, 'year') as add_3_years, DATE_DIFF_MILLIS(1463025540000, 1463284740000, 'day') as sub_3_days, DATE_DIFF_MILLIS(1368590340000, 1463284740000, 'year') as sub_3_years;
Results:
[ { "add_3_days": 3, "add_3_years": 3, "sub_3_days": -3, "sub_3_years": -3 } ]
DATE_DIFF_STR(date1, date2, part)
- Description
-
Finds the elapsed time between two dates specified as formatted strings. This elapsed time is measured from the date specified by
date2
to the date specified bydate1
. Ifdate1
is greater thandate2
then the value returned will be positive, otherwise the value returned will be negative. - Arguments
-
- date1
-
An integer, or any valid expression which evaluates to an integer, representing a Epoch/UNIX timestamp in milliseconds. This is the value that is subtracted from
date1
.If this argument is not an integer, then
null
is returned. - date2
-
An integer, or any valid expression which evaluates to an integer, representing a Epoch/UNIX timestamp in milliseconds.
This is the value that is subtracted from
date1
.If this argument is not an integer, then
null
is returned. - part
-
A string, or any valid expression which evaluates to a string, representing the component of the date to increment.
If an invalid part is passed to the function, then
null
is returned.
- Return Value
-
An integer representing the elapsed time (based on the specified
part
) between both dates. - Examples
-
Example 1: Find the day difference and year difference between two strings.
SELECT DATE_DIFF_STR('2016-05-18T03:59:00Z', '2016-05-15 03:59:00Z', 'day') as add_3_days, DATE_DIFF_STR('2019-05-15T03:59:00Z', '2016-05-15 03:59:00Z', 'year') as add_3_years, DATE_DIFF_STR('2016-05-12T03:59:00Z', '2016-05-15 03:59:00Z', 'day') as sub_3_days, DATE_DIFF_STR('2013-05-15T03:59:00Z', '2016-05-15 03:59:00Z', 'year') as sub_3_years;
Results:
[ { "add_3_days": 3, "add_3_years": 3, "sub_3_days": -3, "sub_3_years": -3 } ]
Example 2: List all hotel documents that were reviewed between two dates.
SELECT name, reviews[0].date FROM `travel-sample`.inventory.hotel WHERE reviews[0].date BETWEEN "2013-01-01 00:00:00 +0100" AND "2014-01-01 00:00:00 +0100";
The same as:
SELECT name, reviews[0].date FROM `travel-sample`.inventory.hotel WHERE reviews[0].date BETWEEN "2013-01-01 %" AND "2014-01-01 %";
Results[ { "date": "2013-06-22 18:33:50 +0300", "name": "Medway Youth Hostel" }, { "date": "2013-06-13 01:39:18 +0300", "name": "Le Clos Fleuri" }, { "date": "2013-07-12 12:18:02 +0300", "name": "Glasgow Grand Central" }, { "date": "2013-07-02 14:32:55 +0300", "name": "The George Hotel" }, { "date": "2013-12-05 09:27:07 +0300", "name": "Bacara Resort & Spa" }, { "date": "2013-01-10 12:48:39 +0300", "name": "Once Brewed YHA Hostel" }, { "date": "2013-09-12 02:57:37 +0300", "name": "Sandy Patch" }, { "date": "2013-12-18 22:36:14 +0300", "name": "The Granary at Roch Mill" }, { "date": "2013-05-08 17:54:41 +0300", "name": "Alberta House B&B" }, ... ]
When querying between two dates, you must specify the full date (with time and time zone) or use the wildcard character (%).
DATE_FORMAT_STR(date1, fmt)
- Description
-
Converts datetime strings from one supported date string format to a different supported date string format.
- Arguments
-
- date1
-
A string, or any valid expression which evaluates to a string, representing a date in a supported date format.
If this argument is not a valid date string then
null
is returned. - fmt
-
A string, or any valid expression which evaluates to a string, representing a supported date format to output the result as.
If an incorrect format is specified then this defaults to the combined full date and time.
- Return Value
-
A date string in the format specified.
- Examples
-
SELECT DATE_FORMAT_STR('2016-05-15T00:00:23+00:00', '1111-11-11') as full_to_short, DATE_FORMAT_STR('2016-05-15', '1111-11-11T00:00:00+00:00') as short_to_full, DATE_FORMAT_STR('01:10:05', '1111-11-11T01:01:01Z') as time_to_full;
Results:
[ { "full_to_short": "2016-05-15", "short_to_full": "2016-05-15T00:00:00-07:00", "time_to_full": "0000-01-01T01:10:05-08:00" } ]
DATE_PART_MILLIS(date1, part [, tz])
- Description
-
Extracts the value of a given date component from an Epoch/UNIX timestamp value.
- Arguments
-
- date1
-
An integer, or any valid expression which evaluates to an integer, representing a Epoch/UNIX timestamp in milliseconds. This is the value that is subtracted from
date1
.If this argument is not an integer, then
null
is returned. - part
-
A string, or any valid expression which evaluates to a string, representing the component of the date to increment.
If an invalid part is passed to the function, then
null
is returned. - tz
-
A string, or any valid expression which evaluates to a string, representing the timezone to convert the local time to.
Optional argument, defaults to the system timezone if not specified. If an incorrect time zone is provided, then
null
is returned.
- Return Value
-
An integer representing the value of the component extracted from the timestamp.
- Examples
-
SELECT DATE_PART_MILLIS(1463284740000, 'day') as day_local, DATE_PART_MILLIS(1463284740000, 'day', 'America/Tijuana') as day_pst, DATE_PART_MILLIS(1463284740000, 'day', 'UTC') as day_utc, DATE_PART_MILLIS(1463284740000, 'month') as month, DATE_PART_MILLIS(1463284740000, 'week') as week, DATE_PART_MILLIS(1463284740000, 'year') as year;
Results:
[ { "day_local": 14, "day_pst": 14, "day_utc": 15, "month": 5, "week": 20, "year": 2016 } ]
DATE_PART_STR(date1, part)
- Description
-
Extracts the value of a given date component from a date string.
- Arguments
-
- date1
-
An integer, or any valid expression which evaluates to an integer, representing a Epoch/UNIX timestamp in milliseconds. This is the value that is subtracted from
date1
.If this argument is not an integer, then
null
is returned. - part
-
A string, or any valid expression which evaluates to a string, representing the component of the date to increment.
If an invalid part is passed to the function, then
null
is returned.
- Return Value
-
An integer representing the value of the component extracted from the timestamp.
- Examples
-
SELECT DATE_PART_STR('2016-05-15T03:59:00Z', 'day') as day, DATE_PART_STR('2016-05-15T03:59:00Z', 'millisecond') as millisecond, DATE_PART_STR('2016-05-15T03:59:00Z', 'month') as month, DATE_PART_STR('2016-05-15T03:59:00Z', 'week') as week, DATE_PART_STR('2016-05-15T03:59:00Z', 'year') as year;
Results:
[ { "day": 15, "millisecond": 0, "month": 5, "week": 20, "year": 2016 } ]
DATE_RANGE_MILLIS(date1, date2, part [,n])
- Description
-
Generates an array of dates from the start date specified by
date1
and the end date specified bydate2
, as Epoch/UNIX timestamps. The difference between each subsequent generated date can be adjusted. - Arguments
-
- date1
-
An integer, or any valid expression which evaluates to an integer, representing a Epoch/UNIX timestamp in milliseconds. This is the value that is subtracted from
date1
.If this argument is not an integer, then
null
is returned. - date2
-
An integer, or any valid expression which evaluates to an integer, representing a Epoch/UNIX timestamp in milliseconds.
This is the value that is subtracted from
date1
.If this argument is not an integer, then
null
is returned. - part
-
A string, or any valid expression which evaluates to a string, representing the component of the date to increment.
If an invalid part is passed to the function, then
null
is returned. - n
-
An integer, or any valid expression which evaluates to an integer, representing the value by which to increment the part component for each generated date.
Optional argument, if not specified, this defaults to 1. If a value which is not an integer is specified, then
null
is returned.
- Return Value
-
An array of integers representing the generated dates, as Epoch/UNIX timestamps, between
date1
anddate2
. - Limitations
-
It is possible to generate very large arrays using this function. In some cases the query engine may be unable to process all of these and cause excessive resource consumption. It is therefore recommended that you first validate the inputs to this function to ensure that the generated result is a reasonable size.
If the start date is greater than the end date passed to the function then an error will not be thrown, but the result array will be empty. An array of descending dates can be generated by setting the start date greater than the end date and specifying a negative value for
n
. - Examples
-
Example 1: Range of milliseconds by month.
SELECT DATE_RANGE_MILLIS(1480752000000, 1475478000000, 'month', -1) as Milliseconds;
Results:
[ { "Milliseconds": [ 1480752000000, 1478156400000 ] } ]
Example 1b: Range of milliseconds by previous month.
SELECT DATE_RANGE_MILLIS(1480752000000, 1449129600000, 'month', -1) as Months;
Results:
[ { "Months": [ 1480752000000, 1478156400000, 1475478000000, 1472886000000, 1470207600000, 1467529200000, 1464937200000, 1462258800000, 1459666800000, 1456992000000, 1454486400000, 1451808000000 ] } ]
DATE_RANGE_STR(start_date, end_date, date_interval [, quantity_int ])
- Description
-
Generates an array of date strings between the start date and end date, calculated by the interval and quantity values. The input dates can be in any of the supported date formats.
- Arguments
-
- start_date
-
A string, or any valid expression which evaluates to a string, representing a date in a supported date format. This is the date used as the start date of the array generation.
If this argument is not an integer, then
null
is returned. - end_date
-
A string, or any valid expression which evaluates to a string, representing a date in a supported date format. This is the date used as the end date of the array generation, and this value is exclusive, that is, the end date will not be included in the result.
If this argument is not an integer, then
null
is returned. - date_interval
-
A string, or any valid expression which evaluates to a string, representing the component of the date to increment.
If an invalid part is passed to the function, then
null
is returned. - quantity_int
-
An integer, or any valid expression which evaluates to an integer, representing the value by which to increment the interval component for each generated date.
Optional argument, if not specified, this defaults to 1. If a value which is not an integer is specified, then
null
is returned.
- Return Value
-
An array of strings representing the generated dates, as date strings, between
start_date
andend_date
. - Limitations
-
-
It is possible to generate very large arrays using this function. In some cases the query engine may be unable to process all of these and cause excessive resource consumption. It is therefore recommended that you first validate the inputs of this function to ensure that the generated result is a reasonable size.
-
If the
start_date
is greater than theend_date
, then an error will not be thrown, but the result array will be empty. An array of descending dates can be generated by setting thestart_date
greater than theend_date
and specifying a negative value forquantity_number
. -
From 4.6.2, both specified dates can be different acceptable date formats; but prior to 4.6.2, both specified dates must have the same string format, otherwise
null
will be returned. To ensure that both dates have the same format, you should use DATE_FORMAT_STR().
-
- Examples
-
Example 1: Ranges by quarters.
SELECT DATE_RANGE_STR('2015-11-30T15:04:05.999', '2017-04-14T15:04:06.998', 'quarter') AS Quarters;
Results:
[ { "Quarters": [ "2015-11-30T15:04:05.999", "2016-03-01T15:04:05.999", "2016-06-01T15:04:05.999", "2016-09-01T15:04:05.999", "2016-12-01T15:04:05.999", "2017-03-01T15:04:05.999" ] } ]
Example 2: Ranges by a single day.
SELECT DATE_RANGE_STR('2016-01-01T15:04:05.999', '2016-01-05T15:04:05.998', 'day', 1) as Days;
Results:
[ { "Days": [ "2016-01-01T15:04:05.999", "2016-01-02T15:04:05.999", "2016-01-03T15:04:05.999", "2016-01-04T15:04:05.999" ] } ]
Example 3: Ranges by four months.
SELECT DATE_RANGE_STR('2018-01-01','2019-01-01', 'month', 4) as Months;
Results:
[ { "Months": [ "2018-01-01", "2018-05-01", "2018-09-01" ] } ]
Example 4: Ranges by previous days.
SELECT DATE_RANGE_STR('2016-01-05T15:04:05.999', '2016-01-01T15:04:06.998', 'day', -1) as Previous;
Results:
[ { "Previous": [ "2016-01-05T15:04:05.999", "2016-01-04T15:04:05.999", "2016-01-03T15:04:05.999", "2016-01-02T15:04:05.999" ] } ]
Example 5: Ranges by month.
SELECT DATE_RANGE_STR('2015-01-01T01:01:01', '2015-12-11T00:00:00', 'month', 1) as Months;
Results:
[ { "Months": [ "2015-01-01T01:01:01", "2015-02-01T01:01:01", "2015-03-01T01:01:01", "2015-04-01T01:01:01", "2015-05-01T01:01:01", "2015-06-01T01:01:01", "2015-07-01T01:01:01", "2015-08-01T01:01:01", "2015-09-01T01:01:01", "2015-10-01T01:01:01", "2015-11-01T01:01:01", "2015-12-01T01:01:01" ] } ]
DATE_TRUNC_MILLIS(date1, part)
- Description
-
Truncates an Epoch/UNIX timestamp up to the specified date component.
- Arguments
-
- date1
-
An integer, or any valid expression which evaluates to an integer, representing a Epoch/UNIX timestamp in milliseconds. This is the date used as the date to truncate.
If this argument is not an integer, then
null
is returned. - part
-
A string, or any valid expression which evaluates to a string, representing the component to truncate to.
If an invalid part is specified, then
null
is returned.
- Return Value
-
An integer representing the truncated timestamp in Epoch/UNIX time.
- Limitations
-
In some cases, where the timestamp is smaller than the duration of the provided part, this function returns the incorrect result. It is recommended that you do not use this function for very small Epoch/UNIX timestamps.
- Examples
-
SELECT DATE_TRUNC_MILLIS(1463284740000, 'day') as day, DATE_TRUNC_MILLIS(1463284740000, 'month') as month, DATE_TRUNC_MILLIS(1463284740000, 'year') as year;
Results:
[ { "day": 1463270400000, "month": 1462147200000, "year": 1451696400000 } ]
DATE_TRUNC_STR(date1, part)
- Description
-
Truncates a date string up to the specified date component.
- Arguments
-
- date1
-
A string, or any valid expression which evaluates to a string, representing a date in a supported date format. This is the date that is truncated.
If this argument is not a valid date format, then
null
is returned. - part
-
A string, or any valid expression which evaluates to a string, representing the component to truncate to.
If an invalid part is specified, then
null
is returned.
- Return Value
-
A date string representing the truncated date.
- Examples
-
SELECT DATE_TRUNC_STR('2016-05-18T03:59:00Z', 'day') as day, DATE_TRUNC_STR('2016-05-18T03:59:00Z', 'month') as month, DATE_TRUNC_STR('2016-05-18T03:59:00Z', 'year') as year;
Results:
[ { "day": "2016-05-18T00:00:00Z", "month": "2016-05-01T00:00:00Z", "year": "2016-01-01T00:00:00Z" } ]
DURATION_TO_STR(duration)
- Description
-
Converts a number into a human-readable time duration with units.
- Arguments
-
- duration
-
A number, or any valid expression which evaluates to a number, which represents the duration to convert to a string. This value is specified in nanoseconds (
1x10-9 seconds
).If a value which is not a number is specified, then
null
is returned.
- Return Value
-
A string representing the human-readable duration.
- Examples
-
SELECT DURATION_TO_STR(2000) as microsecs, DURATION_TO_STR(2000000) as millisecs, DURATION_TO_STR(2000000000) as secs;
Results:
[ { "microsecs": "2µs", "millisecs": "2ms", "secs": "2s" } ]
MILLIS(date1)
- Description
-
Converts a date string to Epoch/UNIX milliseconds.
- Arguments
-
- date1
-
A string, or any valid expression which evaluates to a string, representing a date in a supported date format. This is the date to convert to Epoch/UNIX milliseconds.
If this argument is not a valid date format. then
null
is returned.
- Return Value
-
An integer representing the date string converted to Epoch/UNIX milliseconds.
- Examples
-
SELECT MILLIS("2016-05-15T03:59:00Z") as DateStringInMilliseconds;
Results:
[ { "DateStringInMilliseconds": 1463284740000 } ]
MILLIS_TO_LOCAL(date1 [, fmt])
Alias for MILLIS_TO_STR().
MILLIS_TO_STR(date1 [, fmt ])
- Description
-
Converts an Epoch/UNIX timestamp into the specified date string format.
- Arguments
-
- date1
-
An integer, or any valid expression which evaluates to an integer, representing a Epoch/UNIX timestamp in milliseconds. This is the date to convert.
If this argument is not an integer, then
null
is returned. - fmt
-
A string, or any valid expression which evaluates to a string, representing a supported date format to output the result as.
Optional argument, if unspecified or an incorrect format is specified, then this defaults to the combined full date and time.
- Return Value
-
A date string representing the local date in the specified format.
- Limitations
-
In some cases, where the timestamp is smaller than the duration of the provided part, this function returns the incorrect result. It is recommended that you do not use this function for very small Epoch/UNIX timestamps.
- Examples
-
SELECT MILLIS_TO_STR(1463284740000) as full_date, MILLIS_TO_STR(1463284740000, 'invalid format') as invalid_format, MILLIS_TO_STR(1463284740000, '1111-11-11') as short_date;
Results:
[ { "full_date": "2016-05-14T20:59:00-07:00", "invalid_format": "2016-05-14T20:59:00-07:00", "short_date": "2016-05-14" } ]
MILLIS_TO_TZ(date1, tz [, fmt])
- Description
-
Converts an Epoch/UNIX timestamp into the specified time zone in the specified date string format.
- Arguments
-
- date1
-
An integer, or any valid expression which evaluates to an integer, representing a Epoch/UNIX timestamp in milliseconds. This is the date to convert.
If this argument is not an integer, then
null
is returned. - tz
-
A string, or any valid expression which evaluates to a string, representing the timezone to convert the local time to. Optional argument, defaults to the system timezone if not specified.
If an incorrect time zone is provided, then
null
is returned. - fmt
-
A string, or any valid expression which evaluates to a string, representing a supported date format to output the result as.
Optional argument, if no format or an incorrect format is specified, then this defaults to the combined full date and time.
- Return Value
-
A date string representing the date in the specified timezone in the specified format..
- Examples
-
SELECT MILLIS_TO_TZ(1463284740000, 'America/New_York') as est, MILLIS_TO_TZ(1463284740000, 'Asia/Kolkata') as ist, MILLIS_TO_TZ(1463284740000, 'UTC') as utc;
Results:
[ { "est": "2016-05-14T23:59:00-04:00", "ist": "2016-05-15T09:29:00+05:30", "utc": "2016-05-15T03:59:00Z" } ]
MILLIS_TO_UTC(date1 [, fmt])
- Description
-
Converts an Epoch/UNIX timestamp into local time in the specified date string format.
- Arguments
-
- date1
-
An integer, or any valid expression which evaluates to an integer, representing a Epoch/UNIX timestamp in milliseconds. This is the date to convert to UTC.
If this argument is not an integer, then
null
is returned. - fmt
-
A string, or any valid expression which evaluates to a string, representing a supported date format to output the result as.
Optional argument, if unspecified or an incorrect format is specified, then this defaults to the combined full date and time.
- Return Value
-
A date string representing the date in UTC in the specified format.
- Examples
-
SELECT MILLIS_TO_UTC(1463284740000) as full_date, MILLIS_TO_UTC(1463284740000, 'invalid format') as invalid_format, MILLIS_TO_UTC(1463284740000, '1111-11-11') as short_date;
Results:
[ { "full_date": "2016-05-15T03:59:00Z", "invalid_format": "2016-05-15T03:59:00Z", "short_date": "2016-05-15" } ]
MILLIS_TO_ZONE_NAME(date1, tz [, fmt])
Alias for MILLIS_TO_TZ()
NOW_LOCAL([fmt])
- Description
-
The timestamp of the query as date string in the system timezone. Will not vary during a query.
- Arguments
-
- fmt
-
A string, or any valid expression which evaluates to a string, representing a supported date format to output the result as.
Optional argument, if no format or an incorrect format is specified, then this defaults to the combined full date and time.
- Return Value
-
A date time string in the format specified.
- Limitations
-
If this function is called multiple times within the same query it will always return the same time. If you wish to use the system time when the function is evaluated, use CLOCK_LOCAL() instead.
- Examples
-
Example 1: Various arguments of NOW_LOCAL().
SELECT NOW_LOCAL() as full_date, NOW_LOCAL('invalid date') as invalid_date, NOW_LOCAL('1111-11-11') as short_date;
Results:
[ { "full_date": "2018-01-23T14:03:40.26-08:00", "invalid_date": "2018-01-23T14:03:40.26-08:00", "short_date": "2018-01-23" } ]
Example 2: Difference between NOW_LOCAL() and CLOCK_LOCAL().
SELECT NOW_LOCAL(), NOW_LOCAL(), NOW_LOCAL(), NOW_LOCAL(), NOW_LOCAL(), CLOCK_LOCAL();
Results:
[ { "$1": "2018-01-23T14:06:20.254-08:00", "$2": "2018-01-23T14:06:20.254-08:00", "$3": "2018-01-23T14:06:20.254-08:00", "$4": "2018-01-23T14:06:20.254-08:00", "$5": "2018-01-23T14:06:20.254-08:00", "$6": "2018-01-23T14:06:20.256-08:00" } ]
NOW_MILLIS()
- Description
-
The timestamp of the query as an Epoch/UNIX timestamp. Will not vary during a query.
- Arguments
-
This function accepts no arguments.
- Return Value
-
A floating point number representing the Epoch/UNIX timestamp of the query.
- Limitations
-
If this function is called multiple times within the same query it will always return the same time. If you wish to use the system time when the function is evaluated, use CLOCK_MILLIS() instead.
- Examples
-
Example 1: The time now in milliseconds.
SELECT NOW_MILLIS() as NowInMilliseconds;
Results:
[ { "NowInMilliseconds": 1516745378065.12 } ]
Example 2: Difference between NOW_MILLIS() and CLOCK_MILLIS().
SELECT NOW_MILLIS(), NOW_MILLIS(), NOW_MILLIS(), NOW_MILLIS(), CLOCK_MILLIS();
Results:
[ { "$1": 1516745528579.607, "$2": 1516745528579.607, "$3": 1516745528579.607, "$4": 1516745528580.29 } ]
NOW_TZ(tz [, fmt])
- Description
-
The timestamp of the query as date string in the specified timezone. Will not vary during a query.
- Arguments
-
- tz
-
A string, or any valid expression which evaluates to a string, representing the timezone to convert the query timestamp to.
If an incorrect time zone is provided then
null
is returned. - fmt
-
A string, or any valid expression which evaluates to a string, representing a supported date format to output the result as.
Optional argument, if unspecified or an incorrect format is specified, then this defaults to the combined full date and time.
- Return Value
-
A date string in the format specified representing the timestamp of the query in the specified timezone.
- Limitations
-
If this function is called multiple times within the same query it will always return the same time. If you wish to use the system time when the function is evaluated, use CLOCK_TZ() instead.
- Examples
-
Example 1: Various arguments for NOW_TZ().
SELECT NOW_TZ('invalid tz') as invalid_tz, NOW_TZ('Asia/Kolkata') as ist, NOW_TZ('UTC') as utc, NOW_TZ('UTC', '1111-11-11') as utc_short_date;
Results:
[ { "invalid_tz": null, "ist": "2018-01-24T03:43:36.457+05:30", "utc": "2018-01-23T22:13:36.457Z", "utc_short_date": "2018-01-23" } ]
Example 2: Difference between NOW_TZ() and CLOCK_TZ().
SELECT NOW_TZ('UTC'), NOW_TZ('UTC'), NOW_TZ('UTC'), CLOCK_TZ('UTC');
Results:
[ { "$1": "2018-01-23T22:15:59.551Z", "$2": "2018-01-23T22:15:59.551Z", "$3": "2018-01-23T22:15:59.551Z", "$4": "2018-01-23T22:15:59.552Z" } ]
NOW_STR([fmt])
- Description
-
The timestamp of the query as date string in the system timezone. Will not vary during a query.
- Arguments
-
- fmt
-
A string, or any valid expression which evaluates to a string, representing a supported date format to output the result as.
Optional argument, if unspecified or an incorrect format is specified, then this defaults to the combined full date and time.
- Return Value
-
A date string in the format specified representing the timestamp of the query.
- Limitations
-
If this function is called multiple times within the same query it will always return the same time. If you wish to use the system time when the function is evaluated, use CLOCK_STR() instead.
- Examples
-
Example 1: Various arguments for NOW_STR().
SELECT NOW_STR() as full_date, NOW_STR('invalid date') as invalid_date, NOW_STR('1111-11-11') as short_date;
Results:
[ { "full_date": "2018-01-23T14:16:58.075-08:00", "invalid_date": "2018-01-23T14:16:58.075-08:00", "short_date": "2018-01-23" } ]
Example 2: Difference between NOW_STR() and CLOCK_STR().
SELECT NOW_STR(), NOW_STR(), NOW_STR(), NOW_STR(), NOW_STR(), NOW_STR(), CLOCK_STR();
Results:
[ { "$1": "2018-01-23T14:18:37.605-08:00", "$2": "2018-01-23T14:18:37.605-08:00", "$3": "2018-01-23T14:18:37.605-08:00", "$4": "2018-01-23T14:18:37.605-08:00", "$5": "2018-01-23T14:18:37.605-08:00", "$6": "2018-01-23T14:18:37.605-08:00", "$7": "2018-01-23T14:18:37.607-08:00" } ]
NOW_UTC([fmt])
- Description
-
The timestamp of the query as date string in UTC. Will not vary during a query.
- Arguments
-
- fmt
-
A string, or any valid expression which evaluates to a string, representing a supported date format to output the result as.
Optional argument, if unspecified or an incorrect format is specified, then this defaults to the combined full date and time.
- Return Value
-
A date string in the format specified representing the timestamp of the query in UTC.
- Limitations
-
If this function is called multiple times within the same query it will always return the same time. If you wish to use the system time when the function is evaluated, use CLOCK_MILLIS() instead.
- Examples
-
Example 1: The current UTC time.
SELECT NOW_UTC() as CurrentUTC;
Results:
[ { "CurrentUTC": "2018-01-23T22:20:43.971Z" } ]
Example 2: Difference between NOW_UTC() and CLOCK_UTC().
SELECT NOW_UTC(), NOW_UTC(), NOW_UTC(), NOW_UTC(), NOW_UTC(), NOW_UTC(), NOW_UTC(), CLOCK_UTC();
Results:
[ { "$1": "2018-01-23T22:21:46.769Z", "$2": "2018-01-23T22:21:46.769Z", "$3": "2018-01-23T22:21:46.769Z", "$4": "2018-01-23T22:21:46.769Z", "$5": "2018-01-23T22:21:46.769Z", "$6": "2018-01-23T22:21:46.769Z", "$7": "2018-01-23T22:21:46.769Z", "$8": "2018-01-23T22:21:46.77Z" } ]
STR_TO_DURATION(duration)
- Description
-
Converts a string representation of a time duration into nanoseconds. This accepts the following units:
-
nanoseconds (
ns
) -
microseconds (
us
orµs
) -
milliseconds (
ms
) -
seconds (
s
) -
minutes (
m
) -
hours (
h
)
-
- Arguments
-
- duration
-
A string, or any valid expression which evaluates to a string, representing the duration to convert.
If an invalid duration string is specified, then
null
is returned.
- Return Value
-
A single integer representing the duration in nanoseconds.
- Examples
-
SELECT STR_TO_DURATION('1h') as hour, STR_TO_DURATION('1us') as microsecond, STR_TO_DURATION('1ms') as millisecond, STR_TO_DURATION('1m') as minute, STR_TO_DURATION('1ns') as nanosecond, STR_TO_DURATION('1s') as second;
Results:
[ { "hour": 3600000000000, "microsecond": 1000, "millisecond": 1000000, "minute": 60000000000, "nanosecond": 1, "second": 1000000000 } ]
STR_TO_MILLIS(date1)
- Description
-
Converts a date string to Epoch/UNIX milliseconds.
- Arguments
-
- date1
-
A string, or any valid expression which evaluates to a string, representing a date in a supported date format. This is the date to convert to Epoch/UNIX milliseconds.
If this argument is not a valid date format, then
null
is returned.
- Return Value
-
An integer representing the date string converted to Epoch/UNIX milliseconds.
- Examples
-
SELECT STR_TO_MILLIS("2016-05-15T03:59:00Z") as Milliseconds;
Results:
[ { "Milliseconds": 1463284740000 } ]
STR_TO_UTC(date1)
- Description
-
Converts a date string into the equivalent date in UTC. The output date format follows the date format of the date passed as input.
- Arguments
-
- date1
-
A string, or any valid expression which evaluates to a string, representing a date in a supported date format. This is the date to convert to UTC.
If this argument is not a valid date format, then
null
is returned.
- Return Value
-
A single date string representing the date string converted to UTC.
- Examples
-
SELECT STR_TO_UTC('1111-11-11T00:00:00+08:00') as full_date, STR_TO_UTC('1111-11-11') as short_date;
Results:
[ { "full_date": "1111-11-10T16:00:00Z", "short_date": "1111-11-11" } ]
STR_TO_TZ(date1, tz)
- Description
-
Converts a date string to its equivalent in the specified timezone. The output date format follows the date format of the date passed as input.
- Arguments
-
- date1
-
A string, or any valid expression which evaluates to a string, representing a date in a supported date format. This is the date to convert to UTC.
If this argument is not a valid date format then
null
is returned. - tz
-
A string, or any valid expression which evaluates to a string, representing the timezone to convert the local time to.
If this argument is not a valid timezone, then
null
is returned.
- Return Value
-
A single date string representing the date string converted to the specified timezone.
- Examples
-
SELECT STR_TO_TZ('1111-11-11T00:00:00+08:00', 'America/New_York') as est, STR_TO_TZ('1111-11-11T00:00:00+08:00', 'UTC') as utc, STR_TO_TZ('1111-11-11', 'UTC') as utc_short;
Results:
[ { "est": "1111-11-10T11:00:00-05:00", "utc": "1111-11-10T16:00:00Z", "utc_short": "1111-11-11" } ]
STR_TO_ZONE_NAME(date1, tz)
Alias for STR_TO_TZ().
WEEKDAY_MILLIS(expr [, tz ])
- Description
-
Converts a date string to its equivalent in the specified timezone. The output date format follows the date format of the date passed as input.
- Arguments
-
- expr
-
An integer, or any valid expression which evaluates to an integer, representing an Epoch/UNIX timestamp in milliseconds.
- tz
-
A string, or any valid expression which evaluates to a string, representing the timezone to for the expr argument.
Optional argument, defaults to the system timezone if not specified. If an incorrect time zone is provided then
null
is returned.
- Return Value
-
A single date string representing the date string converted to the specified timezone.
- Examples
-
SELECT WEEKDAY_MILLIS(1486237655742, 'America/Tijuana') as Day;
Results:
[ { "Day": "Saturday" } ]
WEEKDAY_STR(date)
- Description
-
Returns the day of the week string value from the input date string. Returns the weekday name from the input date in Unix timestamp. Note that his function returns the string value of the day of the week, where DATE_PART_STR() with part = "dow" returns an integer value of the weekday (0-6).
- Arguments
-
- date
-
A string, or any valid expression which evaluates to a string, representing a date in a supported date format. This is the date to convert to UTC.
If this argument is not a valid date format then
null
is returned.
- Return Value
-
The text string name of the day of the week, such as "Monday" or "Friday".
- Examples
-
SELECT WEEKDAY_STR('2017-02-05') as Day;
Results:
[ { "Day": "Sunday" } ]