This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Interval Functions

1: EPOCH
2: TO_CENTURIES
3: TO_DAYS
4: TO_DECADES
5: TO_HOURS
6: TO_MICROSECONDS
7: TO_MILLENNIA
8: TO_MILLISECONDS
9: TO_MINUTES
10: TO_MONTHS
11: TO_SECONDS
12: TO_WEEKS
13: TO_YEARS

This section provides reference information for interval functions in PlaidCloud Lakehouse.

1 - EPOCH

Alias for TO_SECONDS.

2 - TO_CENTURIES

Converts a specified number of centuries into an Interval type.

Accepts positive integers, zero, and negative integers as input.

Analyze Syntax

func.to_centuries(<centuries>)

Analyze Examples

func.to_centuries(2)
+------------------------------------------------------+
| func.to_centuries(2)                                 |
+------------------------------------------------------+
| 200 years                                            |
+------------------------------------------------------+

SQL Syntax

TO_CENTURIES(<centuries>)

Return Type

Interval (represented in years).

SQL Examples

SELECT TO_CENTURIES(2), TO_CENTURIES(0), TO_CENTURIES(-2);

┌───────────────────────────────────────────────────────┐
│ to_centuries(2) │ to_centuries(0) │ to_centuries(- 2) │
├─────────────────┼─────────────────┼───────────────────┤
│ 200 years       │ 00:00:00        │ -200 years        │
└───────────────────────────────────────────────────────┘

3 - TO_DAYS

Converts a specified number of days into an Interval type.

Accepts positive integers, zero, and negative integers as input.

Analyze Syntax

func.to_days(<days>)

Analyze Examples

func.to_days(2)
+------------------------------------------------------+
| func.to_days(2)                                      |
+------------------------------------------------------+
| 200 days                                             |
+------------------------------------------------------+

SQL Syntax

TO_DAYS(<days>)

Return Type

Interval (represented in days).

SQL Examples

SELECT TO_DAYS(2), TO_DAYS(0), TO_DAYS(-2);

┌────────────────────────────────────────┐
│ to_days(2) │ to_days(0) │ to_days(- 2) │
├────────────┼────────────┼──────────────┤
│ 2 days     │ 00:00:00   │ -2 days      │
└────────────────────────────────────────┘

4 - TO_DECADES

Converts a specified number of decades into an Interval type.

Accepts positive integers, zero, and negative integers as input.

Analyze Syntax

func.to_decades(<decades>)

Analyze Examples

func.to_decades(2)
+------------------------------------------------------+
| func.to_decades(2)                                   |
+------------------------------------------------------+
| 20 years                                             |
+------------------------------------------------------+

SQL Syntax

TO_DECADES(<decades>)

Return Type

Interval (represented in years).

SQL Examples

SELECT TO_DECADES(2), TO_DECADES(0), TO_DECADES((- 2));

┌─────────────────────────────────────────────────┐
│ to_decades(2) │ to_decades(0) │ to_decades(- 2) │
├───────────────┼───────────────┼─────────────────┤
│ 20 years      │ 00:00:00      │ -20 years       │
└─────────────────────────────────────────────────┘

5 - TO_HOURS

Converts a specified number of hours into an Interval type.

Accepts positive integers, zero, and negative integers as input.

Analyze Syntax

func.to_hours(<hours>)

Analyze Examples

func.to_hours(2)
+------------------------------------------------------+
| func.to_hours(2)                                     |
+------------------------------------------------------+
| 2:00:00                                              |
+------------------------------------------------------+

SQL Syntax

TO_HOURS(<hours>)

Return Type

Interval (in the format hh:mm:ss).

SQL Examples

SELECT TO_HOURS(2), TO_HOURS(0), TO_HOURS((- 2));

┌───────────────────────────────────────────┐
│ to_hours(2) │ to_hours(0) │ to_hours(- 2) │
├─────────────┼─────────────┼───────────────┤
│ 2:00:00     │ 00:00:00    │ -2:00:00      │
└───────────────────────────────────────────┘

6 - TO_MICROSECONDS

Converts a specified number of microseconds into an Interval type.

Accepts positive integers, zero, and negative integers as input.

Analyze Syntax

func.to_microseconds(<microseconds>)

Analyze Examples

func.to_microseconds(2)
+------------------------------------------------------+
| func.to_microseconds(2)                              |
+------------------------------------------------------+
| 0:00:00.000002                                       |
+------------------------------------------------------+

SQL Syntax

TO_MICROSECONDS(<microseconds>)

Return Type

Interval (in the format hh:mm:ss.sssssss).

SQL Examples

SELECT TO_MICROSECONDS(2), TO_MICROSECONDS(0), TO_MICROSECONDS((- 2));

┌────────────────────────────────────────────────────────────────┐
│ to_microseconds(2) │ to_microseconds(0) │ to_microseconds(- 2) │
├────────────────────┼────────────────────┼──────────────────────┤
│ 0:00:00.000002     │ 00:00:00           │ -0:00:00.000002      │
└────────────────────────────────────────────────────────────────┘

7 - TO_MILLENNIA

Converts a specified number of millennia into an Interval type.

Accepts positive integers, zero, and negative integers as input.

Analyze Syntax

func.to_millennia(<millennia>)

Analyze Examples

func.to_millennia(2)
+------------------------------------------------------+
| func.to_millennia(2)                                 |
+------------------------------------------------------+
| 2000 years                                            |
+------------------------------------------------------+

SQL Syntax

TO_MILLENNIA(<millennia>)

Return Type

Interval (represented in years).

SQL Examples

SELECT TO_MILLENNIA(2), TO_MILLENNIA(0), TO_MILLENNIA((- 2));

┌───────────────────────────────────────────────────────┐
│ to_millennia(2) │ to_millennia(0) │ to_millennia(- 2) │
├─────────────────┼─────────────────┼───────────────────┤
│ 2000 years      │ 00:00:00        │ -2000 years       │
└───────────────────────────────────────────────────────┘

8 - TO_MILLISECONDS

Converts a specified number of milliseconds into an Interval type.

Accepts positive integers, zero, and negative integers as input.

Analyze Syntax

func.to_milliseconds(<milliseconds>)

Analyze Examples

func.to_milliseconds(2)
+------------------------------------------------------+
| func.to_milliseconds(2)                              |
+------------------------------------------------------+
|  0:00:00.002                                         |
+------------------------------------------------------+

SQL Syntax

TO_MILLISECONDS(<milliseconds>)

Return Type

Interval (in the format hh:mm:ss.sss).

SQL Examples

SELECT TO_MILLISECONDS(2), TO_MILLISECONDS(0), TO_MILLISECONDS((- 2));

┌────────────────────────────────────────────────────────────────┐
│ to_milliseconds(2) │ to_milliseconds(0) │ to_milliseconds(- 2) │
├────────────────────┼────────────────────┼──────────────────────┤
│ 0:00:00.002        │ 00:00:00           │ -0:00:00.002         │
└────────────────────────────────────────────────────────────────┘

9 - TO_MINUTES

Converts a specified number of minutes into an Interval type.

Accepts positive integers, zero, and negative integers as input.

Analyze Syntax

func.to_minutes(<minutes>)

Analyze Examples

func.to_minutes(2)
+------------------------------------------------------+
| func.to_minutes(2)                                   |
+------------------------------------------------------+
| 0:02:00                                              |
+------------------------------------------------------+

SQL Syntax

TO_MINUTES(<minutes>)

Return Type

Interval (in the format hh:mm:ss).

SQL Examples

SELECT TO_MINUTES(2), TO_MINUTES(0), TO_MINUTES((- 2));

┌─────────────────────────────────────────────────┐
│ to_minutes(2) │ to_minutes(0) │ to_minutes(- 2) │
├───────────────┼───────────────┼─────────────────┤
│ 0:02:00       │ 00:00:00      │ -0:02:00        │
└─────────────────────────────────────────────────┘

10 - TO_MONTHS

Converts a specified number of months into an Interval type.

Accepts positive integers, zero, and negative integers as input.

Analyze Syntax

func.to_months(<months>)

Analyze Examples

func.to_months(2)
+------------------------------------------------------+
| func.to_months(2)                                    |
+------------------------------------------------------+
| 2 months                                             |
+------------------------------------------------------+

SQL Syntax

TO_MONTHS(<months>)

Return Type

Interval (represented in months).

SQL Examples

SELECT TO_MONTHS(2), TO_MONTHS(0), TO_MONTHS((- 2));

┌──────────────────────────────────────────────┐
│ to_months(2) │ to_months(0) │ to_months(- 2) │
├──────────────┼──────────────┼────────────────┤
│ 2 months     │ 00:00:00     │ -2 months      │
└──────────────────────────────────────────────┘

11 - TO_SECONDS

Converts a specified number of seconds into an Interval type.

Accepts positive integers, zero, and negative integers as input.

Analyze Syntax

func.to_seconds(<seconds>)

Analyze Examples

func.to_seconds(2)
+------------------------------------------------------+
| func.to_seconds(2)                                   |
+------------------------------------------------------+
| 0:00:02                                              |
+------------------------------------------------------+

SQL Syntax

TO_SECONDS(<seconds>)

Aliases

EPOCH

Return Type

Interval (in the format hh:mm:ss).

sQL Examples

SELECT TO_SECONDS(2), TO_SECONDS(0), TO_SECONDS((- 2));

┌─────────────────────────────────────────────────┐
│ to_seconds(2) │ to_seconds(0) │ to_seconds(- 2) │
├───────────────┼───────────────┼─────────────────┤
│ 0:00:02       │ 00:00:00      │ -0:00:02        │
└─────────────────────────────────────────────────┘

12 - TO_WEEKS

Converts a specified number of weeks into an Interval type.

Accepts positive integers, zero, and negative integers as input.

Analyze Syntax

func.to_weeks(<weeks>)

Analyze Examples

func.to_weeks(2)
+------------------------------------------------------+
| func.to_weeks(2)                                     |
+------------------------------------------------------+
| 14 days                                              |
+------------------------------------------------------+

SQL Syntax

TO_WEEKS(<weeks>)

Return Type

Interval (represented in days).

sQL Examples

SELECT TO_WEEKS(2), TO_WEEKS(0), TO_WEEKS((- 2));

┌───────────────────────────────────────────┐
│ to_weeks(2) │ to_weeks(0) │ to_weeks(- 2) │
├─────────────┼─────────────┼───────────────┤
│ 14 days     │ 00:00:00    │ -14 days      │
└───────────────────────────────────────────┘

13 - TO_YEARS

Converts a specified number of years into an Interval type.

Accepts positive integers, zero, and negative integers as input.

Analyze Syntax

func.to_years(<years>)

Analyze Examples

func.to_years(2)
+------------------------------------------------------+
| func.to_years(2)                                     |
+------------------------------------------------------+
| 2 years                                              |
+------------------------------------------------------+

SQL Syntax

TO_YEARS(<years>)

Return Type

Interval (represented in years).

SQL Examples

SELECT TO_YEARS(2), TO_YEARS(0), TO_YEARS((- 2));

┌───────────────────────────────────────────┐
│ to_years(2) │ to_years(0) │ to_years(- 2) │
├─────────────┼─────────────┼───────────────┤
│ 2 years     │ 00:00:00    │ -2 years      │
└───────────────────────────────────────────┘