LogoLogo
Studio
4.2
4.2
  • HarperDB Docs
  • Getting Started
  • Developers
    • Applications
      • Caching
      • Defining Schemas
      • Debugging Applications
      • Define Fastify Routes
      • Example Projects
    • Components
      • Installing
      • Writing Extensions
      • Operations
      • Google Data Studio
      • SDKs
      • Drivers
    • REST
    • Operations API
      • Quick Start Examples
      • Databases and Tables
      • NoSQL Operations
      • Bulk Operations
      • Users and Roles
      • Clustering
      • Custom Functions
      • Components
      • Registration
      • Jobs
      • Logs
      • Utilities
      • Token Authentication
      • SQL Operations
      • Advanced JSON SQL Examples
    • Real-Time
    • Clustering
      • Requirements and Definitions
      • Creating A Cluster User
      • Naming A Node
      • Enabling Clustering
      • Establishing Routes
      • Subscription Overview
      • Managing Subscriptions
      • Things Worth Knowing
      • Certificate Management
    • Security
      • JWT Authentication
      • Basic Authentication
      • Configuration
      • Users & Roles
      • Certificate Management
    • SQL Guide
      • SQL Features Matrix
      • SQL Date Functions
      • SQL Reserved Word
      • SQL Functions
      • SQL JSON Search
      • SQL Geospatial Functions
  • Administration
    • Best Practices and Recommendations
    • Logging
      • Standard Logging
      • Audit Logging
      • Transaction Logging
    • Clone Node
    • Jobs
    • HarperDB Studio
      • Create an Account
      • Log In & Password Reset
      • 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
  • Deployments
    • Configuration File
    • HarperDB CLI
    • Install HarperDB
      • On Linux
    • Upgrade a HarperDB Instance
    • HarperDB Cloud
      • IOPS Impact on Performance
      • Instance Size Hardware Specs
      • Alarms
      • Verizon 5G Wavelength
  • Technical Details
    • Reference
      • Analytics
      • Architecture
      • Content Types
      • Data Types
      • Dynamic Schema
      • HarperDB Headers
      • HarperDB Limits
      • Globals
      • Resource Class
      • Transactions
      • Storage Algorithm
    • Release Notes
      • HarperDB Tucker (Version 4)
        • 4.2.8
        • 4.2.7
        • 4.2.6
        • 4.2.5
        • 4.2.4
        • 4.2.3
        • 4.2.2
        • 4.2.1
        • 4.2.0
        • 4.1.2
        • 4.1.1
        • 4.1.0
        • 4.0.7
        • 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
  • More Help
    • Support
    • Slack
    • Contact Us
Powered by GitBook
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()
  1. Developers
  2. SQL Guide

SQL Date Functions

PreviousSQL Features MatrixNextSQL 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