LogoLogo
4.1
4.1
  • Developer Documentation
  • Install HarperDB
    • On Linux
  • Getting Started
  • Full API Documentation
  • HarperDB Studio
    • Create an Account
    • Log In & Password Reset
    • Resources (Marketplace, Drivers, Tutorials, & Example Code)
    • Organizations
    • Instances
    • Query Instance Data
    • Manage Schemas / Browse Data
    • Manage Charts
    • Manage Clustering
    • Manage Instance Users
    • Manage Instance Roles
    • Manage Functions
    • Instance Metrics
    • Instance Configuration
    • Instance Example Code
    • Enable Mixed Content
  • HarperDB Cloud
    • IOPS Impact on Performance
    • Instance Size Hardware Specs
    • Alarms
    • Verizon 5G Wavelength
  • Security
    • JWT Authentication
    • Basic Authentication
    • Configuration
    • Users & Roles
  • Clustering
    • Requirements and Definitions
    • Creating A Cluster User
    • Naming A Node
    • Enabling Clustering
    • Establishing Routes
    • Subscription Overview
    • Managing Subscriptions
    • Things Worth Knowing
  • Custom Functions
    • Requirements and Definitions
    • Create a Project
    • Define Routes
    • Define Helpers
    • Host A Static Web UI
    • Using NPM and GIT
    • Custom Functions Operations
    • Restarting the Server
    • Debugging a Custom Function
    • Custom Functions Templates
    • Example Projects
  • Add-ons and SDKs
    • Google Data Studio
  • SQL Guide
    • SQL Features Matrix
    • Insert
    • Update
    • Delete
    • Select
    • Joins
    • SQL Date Functions
    • SQL Reserved Word
    • SQL Functions
    • SQL JSON Search
    • SQL Geospatial Functions
      • geoArea
      • geoLength
      • geoDifference
      • geoDistance
      • geoNear
      • geoContains
      • geoEqual
      • geoCrosses
      • geoConvert
  • HarperDB CLI
  • Configuration File
  • Logging
  • Transaction Logging
  • Audit Logging
  • Jobs
  • Upgrade a HarperDB Instance
  • Reference
    • Storage Algorithm
    • Dynamic Schema
    • Data Types
    • Content Types/Data Formats
    • HarperDB Headers
    • HarperDB Limits
  • Support
  • Release Notes
    • HarperDB Tucker (Version 4)
      • 4.1.0
      • 4.0.6
      • 4.0.5
      • 4.0.4
      • 4.0.3
      • 4.0.2
      • 4.0.1
      • 4.0.0
    • HarperDB Monkey (Version 3)
      • 3.3.0
      • 3.2.1
      • 3.2.0
      • 3.1.5
      • 3.1.4
      • 3.1.3
      • 3.1.2
      • 3.1.1
      • 3.1.0
      • 3.0.0
    • HarperDB Penny (Version 2)
      • 2.3.1
      • 2.3.0
      • 2.2.3
      • 2.2.2
      • 2.2.0
      • 2.1.1
    • HarperDB Alby (Version 1)
      • 1.3.1
      • 1.3.0
      • 1.2.0
      • 1.1.0
Powered by GitBook

© HarperDB. All Rights Reserved

On this page
  • CURRENT_DATE()
  • CURRENT_TIME()
  • CURRENT_TIMESTAMP
  • DATE([date_string])
  • DATE_ADD(date, value, interval)
  • DATE_DIFF(date_1, date_2[, interval])
  • DATE_FORMAT(date, format)
  • DATE_SUB(date, value, interval)
  • EXTRACT(date, date_part)
  • GETDATE()
  • GET_SERVER_TIME()
  • OFFSET_UTC(date, offset)
  • NOW()
Export as PDF
  1. SQL Guide

SQL Date Functions

PreviousJoinsNextSQL Reserved Word

Last updated 1 year ago

HarperDB utilizes 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.

When parsing date values passed to SQL date functions in HDB, we first check for formats, then for date-time format and then fall back to new Date(date_string)if a known format is not found.

CURRENT_DATE()

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"
    }

CURRENT_TIME()

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"
    }

CURRENT_TIMESTAMP

Referencing this variable will evaluate as the current Unix Timestamp in milliseconds.

"SELECT CURRENT_TIMESTAMP AS current_timestamp_result" returns
    {
      "current_timestamp_result": 1587568845765
    }

DATE([date_string])

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"
    }

DATE_ADD(date, value, interval)

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
    }

DATE_DIFF(date_1, date_2[, interval])

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
    }

DATE_FORMAT(date, format)

"SELECT DATE_FORMAT(1524412627973, 'YYYY-MM-DD HH:mm:ss')
AS date_format_result" returns
    {
      "date_format_result": "2018-04-22 15:57:07"
    }

DATE_SUB(date, value, interval)

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
    }

EXTRACT(date, date_part)

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"
    }

GETDATE()

Returns the current Unix Timestamp in milliseconds.

"SELECT GETDATE() AS getdate_result" returns
    {
      "getdate_result": 1587568845765
    }

GET_SERVER_TIME()

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"
    }

OFFSET_UTC(date, offset)

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"
    }

NOW()

Returns the current Unix Timestamp in milliseconds.

"SELECT NOW() AS now_result" returns
    {
      "now_result": 1587568845765
    }

Formats and returns a date value in the String format provided. Find more details on accepted format values in the .

Coordinated Universal Time (UTC)
ISO 8601
RFC 2822
moment.js docs