LogoLogo
Studio
4.4
4.4
  • Harper Docs
  • Getting Started
  • Developers
    • Applications
      • Caching
      • Defining Schemas
      • Defining Roles
      • Debugging Applications
      • Define Fastify Routes
      • Web Applications
      • Example Projects
    • Components
      • Managing
      • Reference
      • Built-In Components
    • REST
    • Operations API
      • Quick Start Examples
      • Databases and Tables
      • NoSQL Operations
      • Bulk Operations
      • Users and Roles
      • Clustering
        • Clustering with NATS
      • Custom Functions
      • Components
      • Registration
      • Jobs
      • Logs
      • Utilities
      • Token Authentication
      • SQL Operations
      • Advanced JSON SQL Examples
    • Real-Time
    • Replication/Clustering
      • Sharding
      • Legacy NATS 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
      • mTLS 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
    • Miscellaneous
      • Google Data Studio
      • SDKs
      • Query Optimization
  • Administration
    • Best Practices and Recommendations
    • Logging
      • Standard Logging
      • Audit Logging
      • Transaction Logging
    • Clone Node
    • Compact
    • Jobs
    • Harper Studio
      • Create an Account
      • Log In & Password Reset
      • Organizations
      • Instances
      • Manage Databases / Browse Data
      • Manage Clustering
      • Manage Instance Users
      • Manage Instance Roles
      • Manage Applications
      • Instance Metrics
      • Instance Configuration
      • Enable Mixed Content
  • Deployments
    • Configuration File
    • Harper CLI
    • Install Harper
      • On Linux
    • Upgrade a Harper Instance
    • Harper Cloud
      • IOPS Impact on Performance
      • Instance Size Hardware Specs
      • Alarms
      • Verizon 5G Wavelength
  • Technical Details
    • Reference
      • Analytics
      • Architecture
      • Content Types
      • Data Types
      • Dynamic Schema
      • GraphQL
      • Harper Headers
      • Harper Limits
      • Globals
      • Resource Class
      • Transactions
      • Storage Algorithm
    • Release Notes
      • Harper Tucker (Version 4)
        • 4.4.24
        • 4.4.23
        • 4.4.22
        • 4.4.21
        • 4.4.20
        • 4.4.19
        • 4.4.18
        • 4.4.17
        • 4.4.16
        • 4.4.15
        • 4.4.14
        • 4.4.13
        • 4.4.12
        • 4.4.11
        • 4.4.10
        • 4.4.9
        • 4.4.8
        • 4.4.7
        • 4.4.6
        • 4.4.5
        • 4.4.4
        • 4.4.3
        • 4.4.2
        • 4.4.1
        • 4.4.0
        • 4.3.38
        • 4.3.37
        • 4.3.36
        • 4.3.35
        • 4.3.34
        • 4.3.33
        • 4.3.32
        • 4.3.31
        • 4.3.30
        • 4.3.29
        • 4.3.28
        • 4.3.27
        • 4.3.26
        • 4.3.25
        • 4.3.24
        • 4.3.23
        • 4.3.22
        • 4.3.21
        • 4.3.20
        • 4.3.19
        • 4.3.18
        • 4.3.17
        • 4.3.16
        • 4.3.15
        • 4.3.14
        • 4.3.13
        • 4.3.12
        • 4.3.11
        • 4.3.10
        • 4.3.9
        • 4.3.8
        • 4.3.7
        • 4.3.6
        • 4.3.5
        • 4.3.4
        • 4.3.3
        • 4.3.2
        • 4.3.1
        • 4.3.0
        • 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
        • Tucker
      • 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
  1. Developers
  2. SQL Guide

SQL Date Functions

PreviousSQL Features MatrixNextSQL Reserved Word

Last updated 2 months ago

Harper encourages developers to utilize other querying tools over SQL for performance purposes. Harper SQL is intended for data investigation purposes and uses cases where performance is not a priority. SQL optimizations are on our roadmap for the future.

SQL Date Functions

Harper 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