Working With Date and Time Values

This topic provides an overview of working with dates in Splice Machine, in these sections:

For date and time values to work as expected in your database, you must make sure that all nodes in your cluster are set to the same time zone; otherwise the data you read from your database may differ when you communicate with different servers! Please contact your system administrator if you have any questions about this.

Date and Time Functions

Here is a summary of the  TIMESTAMP functions included in this release of Splice Machine:

Function Description
CURRENT_DATE Returns the current date as a DATE value.
DATE Returns a DATE value from a DATE value, a TIMESTAMP value, a string representation of a date or timestamp value, or a numeric value representing elapsed days since January 1, 1970.
DAY Returns an integer value between 1 and 31 representing the day portion of a DATE value, a TIMESTAMP value, or a string representation of a date or timestamp value.
EXTRACT Extracts various date and time components from a date expression.
LAST_DAY Returns a DATE value representing the date of the last day of the month that contains the input date.
MONTH Returns an integer value between 1 and 12 representing the month portion of a DATE value, a TIMESTAMP value, or a string representation of a date or timestamp value.
MONTH_BETWEEN Returns a decimal number representing the number of months between two dates.
MONTHNAME Returns the month name from a date expression.
NEXT_DAY Returns the date of the next specified day of the week after a specified date.
NOW Returns the current date and time as a TIMESTAMP value.
QUARTER Returns the quarter number (1-4) from a date expression.
TIMESTAMP Returns a timestamp value from a TIMESTAMP value, a string representation of a timestamp value, or a string of digits representing such a value.
TIMESTAMPADD Adds the value of an interval to a TIMESTAMP value and returns the sum as a new timestamp.
TIMESTAMPDIFF Finds the difference between two timestamps, in terms of the specfied interval.
TO_CHAR Returns string formed from a DATE value, using a format specification.
TO_DATE Returns a DATE value formed from an input string representation, using a format specification.
WEEK Returns the week number (1-53) from a date expression.
YEAR Returns an integer value between 1 and 9999 representing the year portion of a DATE value, a TIMESTAMP value, or a string representation of a date or timestamp value.

Splice Machine displays TIME and TIMESTAMP values using the current time zone for the server to which you are connected.

Date Arithmetic

Splice Machine provides simple arithmetic operations addition and subtraction on date and timestamp values. You can:

  • find a future date value by adding an integer number of days to a date value
  • find a past date value by subtracting an integer number of days from a date value
  • subtract two date values to find the difference, in days, between those two values

Here’s the syntax for these inline operations:

   dateValue { "+" | "-" } numDays
|  numDays   '+' dateValue
|  dateValue '-' dateValue

dateValue

TIMESTAMP value. This can be a literal date value, a reference to a date value in a table, or the result of a function that produces a date value as its result.

numDays

An integer value expressing the number of days to add or subtract to a date value.

Result Types

The result type of adding or subtracting a number of days to/from a date value is a date value of the same type (DATE or TIMESTAMP) as the dateValue operand.

The result type of subtracting one date value from another is the number of days between the two dates. This can be a positive or negative integer value.

Notes

A few important notes about these operations:

  • Adding a number of days to a date value is commutative, which means that the order of the dateValue and numDays operands is irrelevant.
  • Subtraction of a number of days from a date value is not commutative: the left-side operand must be a date value.
  • Attempting to add two date values produces an error, as does attempting to use a date value in a multiplication or division operation.

Examples

This section presents several examples of using date arithmetic. We’ll first set up a simple table that stores a string value, a DATE value, and a TIMESTAMP value, and we’ll use those values in our example.

splice> CREATE TABLE date_math (s VARCHAR(30), d DATE, t TIMESTAMP);
0 rows inserted/updated/deleted

splice> INSERT INTO date_math values ('2012-05-23 12:24:36', '1988-12-26', '2000-06-07 17:12:30');
1 row inserted/updated/deleted

Example 1: Add a day to a date column and then to a timestamp column

splice> select d + 1 from date_math;
1
----------
1988-12-27
1 row selected

splice> select 1+t from date_math;
1
----------
2000-06-08 17:12:30.0
1 row selected

Example 2: Subtract a day from a timestamp column

splice> select t - 1 from date_math;
1
-----------------------------
2000-06-06 17:12:30.0
1 row selected

Example 3: Subtract a date column from the result of the CURRENT_DATE function

splice> select current_date - d from date_math;
1
-----------
9551
1 row selected

Example 4: Additional examples using literal values

splice> values  date('2011-12-26') + 1;
1
----------
2011-12-27
1 row selected

splice> values  date('2011-12-26') - 1;
1
----------
2011-12-25
1 row selected

splice> values  timestamp('2011-12-26', '17:13:30') + 1;
1
-----------------------------
2011-12-27 17:13:30.0
1 row selected

splice> values  timestamp('2011-12-26', '17:13:30') - 1;
1
-----------------------------
2011-12-25 17:13:30.0
1 row selected

splice> values  date('2011-12-26') - date('2011-06-05');
1
-----------
204
1 row selected

splice> values  date('2011-06-05') - date('2011-12-26');
1
-----------
-204
1 row selected


splice> values  timestamp('2015-06-07', '05:06:00') - current_date;
1
-----------
108
1 row selected

splice> values  timestamp('2011-06-05', '05:06:00') - date('2011-12-26');
1
-----------
-203
1 row selected

See Also

All of the following are in the SQL Reference Manual: