Comment on page
SQL Date Functions
HarperDB utilizes Coordinated Universal Time (UTC) in all internal SQL operations. This means that date values passed into any of the functions below will be assumed to be in UTC or in a format that can be translated to UTC.
Returns the current date in UTC in
YYYY-MM-DD
String format."SELECT CURRENT_DATE() AS current_date_result" returns
{
"current_date_result": "2020-04-22"
}
Returns the current time in UTC in
HH:mm:ss.SSS
String format."SELECT CURRENT_TIME() AS current_time_result" returns
{
"current_time_result": "15:18:14.639"
}
Referencing this variable will evaluate as the current Unix Timestamp in milliseconds.
"SELECT CURRENT_TIMESTAMP AS current_timestamp_result" returns
{
"current_timestamp_result": 1587568845765
}
Formats and returns the date_string argument in UTC in
YYYY-MM-DDTHH:mm:ss.SSSZZ
String format.If a date_string is not provided, the function will return the current UTC date/time value in the return format defined above.
"SELECT DATE(1587568845765) AS date_result" returns
{
"date_result": "2020-04-22T15:20:45.765+0000"
}
"SELECT DATE(CURRENT_TIMESTAMP) AS date_result2" returns
{
"date_result2": "2020-04-22T15:20:45.765+0000"
}
Adds the defined amount of time to the date provided in UTC and returns the resulting Unix Timestamp in milliseconds. Accepted interval values: Either string value (key or shorthand) can be passed as the interval argument.
Key | Shorthand |
---|---|
years | y |
quarters | Q |
months | M |
weeks | w |
days | d |
hours | h |
minutes | m |
seconds | s |
milliseconds | ms |
"SELECT DATE_ADD(1587568845765, 1, 'days') AS date_add_result" AND
"SELECT DATE_ADD(1587568845765, 1, 'd') AS date_add_result" both return
{
"date_add_result": 1587655245765
}
"SELECT DATE_ADD(CURRENT_TIMESTAMP, 2, 'years')
AS date_add_result2" returns
{
"date_add_result2": 1650643129017
}
Returns the difference between the two date values passed based on the interval as a Number. If an interval is not provided, the function will return the difference value in milliseconds.
Accepted interval values:
- years
- months
- weeks
- days
- hours
- minutes
- seconds
"SELECT DATE_DIFF(CURRENT_TIMESTAMP, 1650643129017, 'hours')
AS date_diff_result" returns
{
"date_diff_result": -17519.753333333334
}
Formats and returns a date value in the String format provided. Find more details on accepted format values in the moment.js docs.
"SELECT DATE_FORMAT(1524412627973, 'YYYY-MM-DD HH:mm:ss')
AS date_format_result" returns
{
"date_format_result": "2018-04-22 15:57:07"
}
Subtracts the defined amount of time from the date provided in UTC and returns the resulting Unix Timestamp in milliseconds. Accepted date_sub interval values- Either string value (key or shorthand) can be passed as the interval argument.
Key | Shorthand |
---|---|
years | y |
quarters | Q |
months | M |
weeks | w |
days | d |
hours | h |
minutes | m |
seconds | s |
milliseconds | ms |
"SELECT DATE_SUB(1587568845765, 2, 'years') AS date_sub_result" returns
{
"date_sub_result": 1524410445765
}
Extracts and returns the date_part requested as a String value. Accepted date_part values below show value returned for date = “2020-03-26T15:13:02.041+000”
date_part | Example return value* |
---|---|
year | “2020” |
month | “3” |
day | “26” |
hour | “15” |
minute | “13” |
second | “2” |
millisecond | “41” |
"SELECT EXTRACT(1587568845765, 'year') AS extract_result" returns
{
"extract_result": "2020"
}
Returns the current Unix Timestamp in milliseconds.
"SELECT GETDATE() AS getdate_result" returns
{
"getdate_result": 1587568845765
}
Returns the current date/time value based on the server’s timezone in
YYYY-MM-DDTHH:mm:ss.SSSZZ
String format."SELECT GET_SERVER_TIME() AS get_server_time_result" returns
{
"get_server_time_result": "2020-04-22T15:20:45.765+0000"
}
Returns the UTC date time value with the offset provided included in the return String value formatted as
YYYY-MM-DDTHH:mm:ss.SSSZZ
. The offset argument will be added as minutes unless the value is less than 16 and greater than -16, in which case it will be treated as hours."SELECT OFFSET_UTC(1587568845765, 240) AS offset_utc_result" returns
{
"offset_utc_result": "2020-04-22T19:20:45.765+0400"
}
"SELECT OFFSET_UTC(1587568845765, 10) AS offset_utc_result2" returns
{
"offset_utc_result2": "2020-04-23T01:20:45.765+1000"
}
Returns the current Unix Timestamp in milliseconds.
"SELECT NOW() AS now_result" returns
{
"now_result": 1587568845765
}
Last modified 4mo ago