Date & time data types¶

Interval literals¶

An interval literal is an expression that specifies a duration of time in a string literal. Use the following syntax to specify an interval literal:

INTERVAL '[ <sign> ] <string>' { <yearMonthQualifier> | <dayTimeQualifier> }

Where:

• sign is an optional symbol that specifies a positive (+) or negative (-) duration of time.

  Default: +.

• string is a value that represents a time duration.

• yearMonthQualifier is a qualifier that is defined in Syntax of interval data types.

• dayTimeQualifier is a qualifier that is defined in Syntax of interval data types.

Interval formats¶

String literals in specific formats can represent interval values.

To specify values for years and months, use the following format:

'<sign><Y>-<MM>'

Where:

• sign is a required symbol that specifies a positive (+) or negative (-) duration of time.

  Default: +.

• Y is the number of years. The number of digits that is allowed (precision) depends on the data type of the value.

• MM is two digits for the number of months, from 00 to 11.

To specify values for days, hours, seconds, and fractional seconds, use the following format:

'<sign>[<D>] [<HH24>]:[<MI>]:[<SS>].[<F>]'

Where:

• sign is a required symbol that specifies a positive (+) or negative (-) duration of time.

  Default: +.

• D is the number of days. The number of digits that is allowed (precision) depends on the data type of the value.

  Omit D for values of the following types:

  • INTERVAL HOUR

  • INTERVAL HOUR TO MINUTE

  • INTERVAL HOUR TO SECOND

  • INTERVAL MINUTE

  • INTERVAL MINUTE TO SECOND

  • INTERVAL SECOND

• HH24 is two digits for the number of hours, from 00 to 23.

  Omit HH24 for values of the following types:

  • INTERVAL DAY

  • INTERVAL MINUTE

  • INTERVAL MINUTE TO SECOND

  • INTERVAL SECOND

• MI is two digits for the number of minutes, from 00 through 59.

  Omit MI for values of the following types:

  • INTERVAL DAY TO HOUR

  • INTERVAL DAY

  • INTERVAL HOUR

  • INTERVAL SECOND

• SS is two digits for the number of seconds, from 00 through 59.

  Omit SS for values of the following types:

  • INTERVAL DAY

  • INTERVAL DAY TO HOUR

  • INTERVAL DAY TO MINUTE

  • INTERVAL HOUR

  • INTERVAL HOUR TO MINUTE

  • INTERVAL MINUTE

• F is the number of fractional seconds for the data types that include seconds. The number of digits that is allowed (precision) depends on the data type of the value.

The following usage notes apply to string literals in interval format:

• The string literal representation applies when you use the CAST or TO_CHAR function to cast intervals explicitly to text strings.

• Leading zeros in a field specify precision.

Examples of interval values¶

The following table shows how to represent various interval values. The values shown in the table conform to the following rules for interval values:

• For positive values, the plus sign + is optional for interval literal values but required for interval format values.

• In the interval literal values, the value in parentheses specifies the precision, which is the number of digits that is allowed. For example, YEAR(3) specifies that three digits are allowed in the year.

• In the interval format values, the primary field (the leading field) does not include leading zeros. Subordinate fields use a fixed number of digits. For example, in a YEAR TO MONTH value like +1-08, the year field has no leading zeros, and the month field uses two digits.

Performing arithmetic by using interval data¶

The following examples perform arithmetic by using interval data.

Add one year and one month to a date:

SELECT TO_DATE('2024-01-01') + INTERVAL '1-1' YEAR TO MONTH
  AS date_plus_one_year_one_month;

+------------------------------+
| DATE_PLUS_ONE_YEAR_ONE_MONTH |
|------------------------------|
| 2025-02-01                   |
+------------------------------+

Subtract one year and one month from a date:

SELECT TO_DATE('2024-01-01') + INTERVAL '-1-1' YEAR TO MONTH
  AS date_plus_one_year_one_month;

+------------------------------+
| DATE_PLUS_ONE_YEAR_ONE_MONTH |
|------------------------------|
| 2022-12-01                   |
+------------------------------+

Add a period of time to a timestamp:

SELECT TO_TIMESTAMP('2024-01-01 08:08:08.99') + INTERVAL '1 01:01:01.7878' DAY TO SECOND
  AS date_plus_period_of_time;

+--------------------------+
| DATE_PLUS_PERIOD_OF_TIME |
|--------------------------|
| 2024-01-02 09:09:10.777  |
+--------------------------+

The following example uses the SYSTEM$TYPEOF function to show that an INTERVAL DAY TO SECOND value is returned when a query subtracts two timestamp values:

SELECT SYSTEM$TYPEOF(TO_TIMESTAMP('2025-10-05 01:02:03') - TO_TIMESTAMP('2025-09-15 11:36:22'))
  AS type;

+------------------------------------+
| TYPE                               |
|------------------------------------|
| INTERVAL DAY(9) TO SECOND(9)[SB16] |
+------------------------------------+

To view the results of the query in interval format, you can cast the expression to the INTERVAL DAY(2) TO SECOND(2) data type to specify precision, and then cast to VARCHAR:

SELECT (TO_TIMESTAMP('2025-10-05 01:02:03') - TO_TIMESTAMP('2025-09-15 11:36:22'))::INTERVAL DAY(2) TO SECOND(2)::VARCHAR
  AS interval_format_result;

+------------------------+
| INTERVAL_FORMAT_RESULT |
|------------------------|
| +19 13:25:41.00        |
+------------------------+

Inserting and querying year-month interval data¶

Create a table that tracks candidates for open positions with an INTERVAL YEAR TO MONTH column, and insert data:

CREATE OR REPLACE TABLE candidates (
  name_first VARCHAR,
  name_last VARCHAR,
  duration_of_experience INTERVAL YEAR(2) TO MONTH);

INSERT INTO candidates VALUES ('Jane', 'Smith', '14-4');
INSERT INTO candidates VALUES ('Robert', 'Adams', '0-3');
INSERT INTO candidates VALUES ('Mary', 'Jones', '5-11');

When you query the table without casting the duration_of_experience` column to a data type, the output shows the column values as the total number of months in each row:

SELECT name_first,
       name_last,
       duration_of_experience AS months_of_experience
  FROM candidates;

+------------+-----------+----------------------+
| NAME_FIRST | NAME_LAST | MONTHS_OF_EXPERIENCE |
|------------+-----------+----------------------|
| Jane       | Smith     |                  172 |
| Robert     | Adams     |                    3 |
| Mary       | Jones     |                   71 |
+------------+-----------+----------------------+

When you query the table and cast the duration_of_experience column to the VARCHAR data type, the output shows the column values in interval format:

SELECT name_first,
       name_last,
       duration_of_experience::VARCHAR AS duration_of_experience
  FROM candidates;

+------------+-----------+------------------------+
| NAME_FIRST | NAME_LAST | DURATION_OF_EXPERIENCE |
|------------+-----------+------------------------|
| Jane       | Smith     | +14-04                 |
| Robert     | Adams     | +0-03                  |
| Mary       | Jones     | +5-11                  |
+------------+-----------+------------------------+

Inserting and querying day-time interval data¶

Create a table that specifies the timeout duration for various software features with an INTERVAL HOUR TO SECOND column, and insert data:

CREATE OR REPLACE TABLE feature_timeouts (
  feature VARCHAR,
  timeout_duration INTERVAL HOUR(2) TO SECOND(0));

INSERT INTO feature_timeouts VALUES ('Feature1', '00:00:30');
INSERT INTO feature_timeouts VALUES ('Feature2', '00:10:00');
INSERT INTO feature_timeouts VALUES ('Feature3', '01:00:00');

Query the table and cast the timeout_duration column to the VARCHAR data type:

SELECT feature,
       timeout_duration::VARCHAR AS timeout_duration
  FROM feature_timeouts;

+----------+------------------+
| FEATURE  | TIMEOUT_DURATION |
|----------+------------------|
| Feature1 | +0:00:30         |
| Feature2 | +0:10:00         |
| Feature3 | +1:00:00         |
+----------+------------------+

Copying interval data into a table and querying the table¶

Complete the following steps to stage a file with interval data, and then copy the file into a table:

1. In a file on your file system, copy the following content:

   Expand

   1,1-2,28 16:15:14.0
   2,-3-2,-54 16:15:14.123
   

   Show lessSee more

   Scroll to top

   This example assumes that the file is named interval_values.csv in the /examples/intervals/ directory.

2. Create a stage:

   CopyExpand

   CREATE STAGE interval_stage;
   

   Show lessSee more

   Scroll to top

3. In the internal staging location, stage the file:

   CopyExpand

   PUT file:///examples/intervals/interval_values.csv @~/interval_stage
     AUTO_COMPRESS=false;
   

   Show lessSee more

   Scroll to top

4. Create a table for the data:

   CopyExpand

   CREATE OR REPLACE TABLE sample_interval_values(
     c1 STRING,
     c2 INTERVAL YEAR(1) TO MONTH,
     c3 INTERVAL DAY(2) TO SECOND(3));
   

   Show lessSee more

   Scroll to top

5. To load the staged file into the table that you created, use the COPY INTO <table> command:

   CopyExpand

   COPY INTO sample_interval_values FROM @~/interval_stage;
   

   Show lessSee more

   Scroll to top

6. To view the loaded data, query the table, and cast to the VARCHAR type to view the loaded data:

   CopyExpand

   SELECT c1,
          c2::VARCHAR AS YEAR_TO_MONTH,
          c3::VARCHAR AS DAY_TO_SECOND,
     FROM sample_interval_values;
   

   Show lessSee more

   Scroll to top
   Expand

   +----+---------------+------------------+
   | C1 | YEAR_TO_MONTH | DAY_TO_SECOND    |
   |----+---------------+------------------|
   | 1  | +1-02         | +28 16:15:14.000 |
   | 2  | -3-02         | -54 16:15:14.123 |
   +----+---------------+------------------+
   

   Show lessSee more

   Scroll to top