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

Return to the regular view of this page.

Date & Time Functions

1: ADD TIME INTERVAL
2: CURRENT_TIMESTAMP
3: DATE
4: DATE DIFF
5: DATE_ADD
6: DATE_FORMAT
7: DATE_PART
8: DATE_SUB
9: DATE_TRUNC
10: DAY
11: EXTRACT
12: MONTH
13: NOW
14: QUARTER
15: STR_TO_DATE
16: STR_TO_TIMESTAMP
17: SUBTRACT TIME INTERVAL
18: TIME_SLOT
19: TIMEZONE
20: TO_DATE
21: TO_DATETIME
22: TO_DAY_OF_MONTH
23: TO_DAY_OF_WEEK
24: TO_DAY_OF_YEAR
25: TO_HOUR
26: TO_MINUTE
27: TO_MONDAY
28: TO_MONTH
29: TO_QUARTER
30: TO_SECOND
31: TO_START_OF_DAY
32: TO_START_OF_FIFTEEN_MINUTES
33: TO_START_OF_FIVE_MINUTES
34: TO_START_OF_HOUR
35: TO_START_OF_ISO_YEAR
36: TO_START_OF_MINUTE
37: TO_START_OF_MONTH
38: TO_START_OF_QUARTER
39: TO_START_OF_SECOND
40: TO_START_OF_TEN_MINUTES
41: TO_START_OF_WEEK
42: TO_START_OF_YEAR
43: TO_TIMESTAMP
44: TO_UNIX_TIMESTAMP
45: TO_WEEK_OF_YEAR
46: TO_YEAR
47: TO_YYYYMM
48: TO_YYYYMMDD
49: TO_YYYYMMDDHH
50: TO_YYYYMMDDHHMMSS
51: TODAY
52: TOMORROW
53: TRY_TO_DATETIME
54: TRY_TO_TIMESTAMP
55: WEEK
56: WEEKOFYEAR
57: YEAR
58: YESTERDAY

This section provides reference information for the datetime-related functions in PlaidCloud Lakehouse.

Conversion Functions

Date Arithmetic Functions

Date Information Functions

Others

1 - ADD TIME INTERVAL

Add time interval function

Add a time interval to a date or timestamp, return the result of date or timestamp type.

Analyze Syntax

func.add_years(<exp0>, <expr1>)
func.add_quarters(<exp0>, <expr1>)
func.add_months(<exp0>, <expr1>)
func.add_days(<exp0>, <expr1>)
func.add_hours(<exp0>, <expr1>)
func.add_minutes(<exp0>, <expr1>)
func.add_seconds(<exp0>, <expr1>)

Analyze Examples

func.to_date(18875), func.add_years(func.to_date(18875), 2)
+---------------------------------+---------------------------------------------------+
| func.to_date(18875)             | func.add_years(func.to_date(18875), 2)            |
+---------------------------------+---------------------------------------------------+
| 2021-09-05                      | 2023-09-05                                        |
+---------------------------------+---------------------------------------------------+

func.to_date(18875), func.add_quarters(func.to_date(18875), 2)
+---------------------------------+---------------------------------------------------+
| func.to_date(18875)             | add_quarters(func.to_date(18875), 2)              |
+---------------------------------+---------------------------------------------------+
| 2021-09-05                      | 2022-03-05                                        |
+---------------------------------+---------------------------------------------------+

func.to_date(18875), func.add_months(func.to_date(18875), 2)
+---------------------------------+---------------------------------------------------+
| func.to_date(18875)             | func.add_months(func.to_date(18875), 2)           |
+---------------------------------+---------------------------------------------------+
| 2021-09-05                      | 2021-11-05                                        |
+---------------------------------+---------------------------------------------------+

func.to_date(18875), func.add_days(func.to_date(18875), 2)
+---------------------------------+---------------------------------------------------+
| func.to_date(18875)             | func.add_days(func.to_date(18875), 2)             |
+---------------------------------+---------------------------------------------------+
| 2021-09-05                      | 2021-09-07                                        |
+---------------------------------+---------------------------------------------------+

func.to_datetime(1630833797), func.add_hours(func.to_datetime(1630833797), 2)
+---------------------------------+---------------------------------------------------+
| func.to_datetime(1630833797)    | func.add_hours(func.to_datetime(1630833797), 2)   |
+---------------------------------+---------------------------------------------------+
| 2021-09-05 09:23:17.000000      | 2021-09-05 11:23:17.000000                        |
+---------------------------------+---------------------------------------------------+

func.to_datetime(1630833797), func.add_minutes(func.to_datetime(1630833797), 2)
+---------------------------------+---------------------------------------------------+
| func.to_datetime(1630833797)    | func.add_minutes(func.to_datetime(1630833797), 2) |
+---------------------------------+---------------------------------------------------+
| 2021-09-05 09:23:17.000000      | 2021-09-05 09:25:17.000000                        |
+---------------------------------+---------------------------------------------------+

func.to_datetime(1630833797), func.add_seconds(func.to_datetime(1630833797), 2)
+---------------------------------+---------------------------------------------------+
| func.to_datetime(1630833797)    | func.add_seconds(func.to_datetime(1630833797), 2) |
+---------------------------------+---------------------------------------------------+
| 2021-09-05 09:23:17.000000      | 2021-09-05 09:23:19.000000                        |
+---------------------------------+---------------------------------------------------+

SQL Syntax

ADD_YEARS(<exp0>, <expr1>)
ADD_QUARTERs(<exp0>, <expr1>)
ADD_MONTHS(<exp0>, <expr1>)
ADD_DAYS(<exp0>, <expr1>)
ADD_HOURS(<exp0>, <expr1>)
ADD_MINUTES(<exp0>, <expr1>)
ADD_SECONDS(<exp0>, <expr1>)

Return Type

DATE, TIMESTAMP, depends on the input.

SQL Examples

SELECT to_date(18875), add_years(to_date(18875), 2);
+----------------+------------------------------+
| to_date(18875) | add_years(to_date(18875), 2) |
+----------------+------------------------------+
| 2021-09-05     | 2023-09-05                   |
+----------------+------------------------------+

SELECT to_date(18875), add_quarters(to_date(18875), 2);
+----------------+---------------------------------+
| to_date(18875) | add_quarters(to_date(18875), 2) |
+----------------+---------------------------------+
| 2021-09-05     | 2022-03-05                      |
+----------------+---------------------------------+

SELECT to_date(18875), add_months(to_date(18875), 2);
+----------------+-------------------------------+
| to_date(18875) | add_months(to_date(18875), 2) |
+----------------+-------------------------------+
| 2021-09-05     | 2021-11-05                    |
+----------------+-------------------------------+

SELECT to_date(18875), add_days(to_date(18875), 2);
+----------------+-----------------------------+
| to_date(18875) | add_days(to_date(18875), 2) |
+----------------+-----------------------------+
| 2021-09-05     | 2021-09-07                  |
+----------------+-----------------------------+

SELECT to_datetime(1630833797), add_hours(to_datetime(1630833797), 2);
+----------------------------+---------------------------------------+
| to_datetime(1630833797)    | add_hours(to_datetime(1630833797), 2) |
+----------------------------+---------------------------------------+
| 2021-09-05 09:23:17.000000 | 2021-09-05 11:23:17.000000            |
+----------------------------+---------------------------------------+

SELECT to_datetime(1630833797), add_minutes(to_datetime(1630833797), 2);
+----------------------------+-----------------------------------------+
| to_datetime(1630833797)    | add_minutes(to_datetime(1630833797), 2) |
+----------------------------+-----------------------------------------+
| 2021-09-05 09:23:17.000000 | 2021-09-05 09:25:17.000000              |
+----------------------------+-----------------------------------------+

SELECT to_datetime(1630833797), add_seconds(to_datetime(1630833797), 2);
+----------------------------+-----------------------------------------+
| to_datetime(1630833797)    | add_seconds(to_datetime(1630833797), 2) |
+----------------------------+-----------------------------------------+
| 2021-09-05 09:23:17.000000 | 2021-09-05 09:23:19.000000              |
+----------------------------+-----------------------------------------+

2 - CURRENT_TIMESTAMP

Alias for NOW.

3 - DATE

Alias for TO_DATE.

4 - DATE DIFF

PlaidCloud Lakehouse does not provide a date_diff function yet, but it supports direct arithmetic operations on dates and times. For example, you can use the expression TO_DATE(NOW())-2 to obtain the date from two days ago.

This flexibility of directly manipulating dates and times in PlaidCloud Lakehouse makes it convenient and versatile for handling date and time computations. See an example below:

CREATE TABLE tasks (
  task_name VARCHAR(50),
  start_date DATE,
  end_date DATE
);

INSERT INTO tasks (task_name, start_date, end_date)
VALUES
  ('Task 1', '2023-06-15', '2023-06-20'),
  ('Task 2', '2023-06-18', '2023-06-25'),
  ('Task 3', '2023-06-20', '2023-06-23');

SELECT task_name, end_date - start_date AS duration
FROM tasks;

task_name|duration|
---------+--------+
Task 1   |       5|
Task 2   |       7|
Task 3   |       3|

5 - DATE_ADD

Add the time interval or date interval to the provided date or date with time (timestamp/datetime).

Analyze Syntax

func.date_add(<unit>, <value>,  <date_or_time_expr>)

Analyze Examples

func.date_add('YEAR', 1, func.to_date('2018-01-02'))
+------------------------------------------------------+
| func.date_add('YEAR', 1, func.to_date('2018-01-02')) |
+------------------------------------------------------+
| 2019-01-02                                           |
+------------------------------------------------------+

SQL Syntax

DATE_ADD(<unit>, <value>,  <date_or_time_expr>)

Arguments

Arguments	Description
`<unit>`	Must be of the following values: `YEAR`, `QUARTER`, `MONTH`, `DAY`, `HOUR`, `MINUTE` and `SECOND`
`<value>`	This is the number of units of time that you want to add. For example, if you want to add 2 days, this will be 2.
`<date_or_time_expr>`	A value of `DATE` or `TIMESTAMP` type

Return Type

The function returns a value of the same type as the <date_or_time_expr> argument.

SQL Examples

Query:

SELECT date_add(YEAR, 1, to_date('2018-01-02'));
+---------------------------------------------------+
| DATE_ADD(YEAR, INTERVAL 1, to_date('2018-01-02')) |
+---------------------------------------------------+
| 2019-01-02                                        |
+---------------------------------------------------+

6 - DATE_FORMAT

Alias for TO_STRING.

7 - DATE_PART

Retrieves the designated portion of a date, time, or timestamp.

Analyze Syntax

func.date_part(<unit>, <date_or_time_expr>)

Analyze Examples

func.now()           |
---------------------+
2023-10-16 02:09:28.0|

func.date_part('day', now())

func.date_part('day', now())|
----------------------------+
                         16 |

SQL Syntax

DATE_PART( YEAR | QUARTER | MONTH | WEEK | DAY | HOUR | MINUTE | SECOND | DOW | DOY, <date_or_time_expr> )

DOW: Day of Week.
DOY: Day of Year.

Return Type

Integer.

SQL Examples

SELECT NOW();

now()                |
---------------------+
2023-10-16 02:09:28.0|

SELECT DATE_PART(DAY, NOW());

date_part(day, now())|
---------------------+
                   16|

-- October 16, 2023, is a Monday
SELECT DATE_PART(DOW, NOW());

date_part(dow, now())|
---------------------+
                    1|

-- October 16, 2023, is the 289th day of the year
SELECT DATE_PART(DOY, NOW());

date_part(doy, now())|
---------------------+
                  289|

SELECT DATE_PART(MONTH, TO_DATE('2022-05-13'));

date_part(month, to_date('2022-05-13'))|
---------------------------------------+
                                      5|

8 - DATE_SUB

Subtract the time interval or date interval from the provided date or date with time (timestamp/datetime).

Analyze Syntax

func.date_sub(<unit>, <value>,  <date_or_time_expr>)

Analyze Examples

func.date_sub('YEAR', 1, func.to_date('2018-01-02'))
+------------------------------------------------------+
| func.date_sub('YEAR', 1, func.to_date('2018-01-02')) |
+------------------------------------------------------+
| 2017-01-02                                           |
+------------------------------------------------------+

SQL Syntax

DATE_SUB(<unit>, <value>,  <date_or_time_expr>)

Arguments

Arguments	Description
`<unit>`	Must be of the following values: `YEAR`, `QUARTER`, `MONTH`, `DAY`, `HOUR`, `MINUTE` and `SECOND`
`<value>`	This is the number of units of time that you want to add. For example, if you want to add 2 days, this will be 2.
`<date_or_time_expr>`	A value of `DATE` or `TIMESTAMP` type

Return Type

The function returns a value of the same type as the <date_or_time_expr> argument.

SQL Examples

Query:

SELECT date_sub(YEAR, 1, to_date('2018-01-02'));
+---------------------------------------------------+
| DATE_SUB(YEAR, INTERVAL 1, to_date('2018-01-02')) |
+---------------------------------------------------+
| 2017-01-02                                        |
+---------------------------------------------------+

9 - DATE_TRUNC

Truncates a date, time, or timestamp value to a specified precision. For example, if you truncate 2022-07-07 to MONTH, the result will be 2022-07-01; if you truncate 2022-07-07 01:01:01.123456 to SECOND, the result will be 2022-07-07 01:01:01.000000.

Analyze Syntax

func.date_sub(<precision>,  <date_or_time_expr>)

Analyze Examples

func.date_trunc('month', func.to_date('2022-07-07'))
+------------------------------------------------------+
| func.date_trunc('month', func.to_date('2022-07-07')) |
+------------------------------------------------------+
| 2022-07-01                                           |
+------------------------------------------------------+

SQL Syntax

DATE_TRUNC(<precision>, <date_or_time_expr>)

Arguments

Arguments	Description
`<precision>`	Must be of the following values: `YEAR`, `QUARTER`, `MONTH`, `DAY`, `HOUR`, `MINUTE` and `SECOND`
`<date_or_time_expr>`	A value of `DATE` or `TIMESTAMP` type

Return Type

The function returns a value of the same type as the <date_or_time_expr> argument.

SQL Examples

select date_trunc(month, to_date('2022-07-07'));
+------------------------------------------+
| date_trunc(month, to_date('2022-07-07')) |
+------------------------------------------+
| 2022-07-01                               |
+------------------------------------------+

10 - DAY

Alias for TO_DAY_OF_MONTH.

11 - EXTRACT

Retrieves the designated portion of a date, time, or timestamp.

SQL Syntax

EXTRACT( YEAR | QUARTER | MONTH | WEEK | DAY | HOUR | MINUTE | SECOND | DOW | DOY FROM <date_or_time_expr> )

DOW: Day of the Week.
DOY: Day of Year.

Return Type

Integer.

SQL Examples

SELECT NOW();

now()                |
---------------------+
2023-10-16 02:09:28.0|

SELECT EXTRACT(DAY FROM NOW());

extract(day from now())|
-----------------------+
                     16|

-- October 16, 2023, is a Monday
SELECT EXTRACT(DOW FROM NOW());

extract(dow from now())|
-----------------------+
                      1|

-- October 16, 2023, is the 289th day of the year
SELECT EXTRACT(DOY FROM NOW());

extract(doy from now())|
-----------------------+
                    289|

SELECT EXTRACT(MONTH FROM TO_DATE('2022-05-13'));

extract(month from to_date('2022-05-13'))|
-----------------------------------------+
                                        5|

12 - MONTH

Alias for TO_MONTH.

13 - NOW

Returns the current date and time.

Analyze Syntax

func.now()

Analyze Examples

┌─────────────────────────────────────────────────────────┐
│  func.current_timestamp()  │         func.now()         │
├────────────────────────────┼────────────────────────────┤
│ 2024-01-29 04:38:12.584359 │ 2024-01-29 04:38:12.584417 │
└─────────────────────────────────────────────────────────┘

SQL Syntax

NOW()

Return Type

TIMESTAMP

Aliases

CURRENT_TIMESTAMP

SQL Examples

This example returns the current date and time:

SELECT CURRENT_TIMESTAMP(), NOW();

┌─────────────────────────────────────────────────────────┐
│     current_timestamp()    │            now()           │
├────────────────────────────┼────────────────────────────┤
│ 2024-01-29 04:38:12.584359 │ 2024-01-29 04:38:12.584417 │
└─────────────────────────────────────────────────────────┘

14 - QUARTER

Alias for TO_QUARTER.

15 - STR_TO_DATE

Alias for TO_DATE.

16 - STR_TO_TIMESTAMP

Alias for TO_TIMESTAMP.

17 - SUBTRACT TIME INTERVAL

Subtract time interval function

Subtract time interval from a date or timestamp, return the result of date or timestamp type.

Analyze Syntax

func.subtract_years(<exp0>, <expr1>)
func.subtract_quarters(<exp0>, <expr1>)
func.subtract_months(<exp0>, <expr1>)
func.subtract_days(<exp0>, <expr1>)
func.subtract_hours(<exp0>, <expr1>)
func.subtract_minutes(<exp0>, <expr1>)
func.subtract_seconds(<exp0>, <expr1>)

Analyze Examples

func.to_date(18875), func.subtract_years(func.to_date(18875), 2)
+---------------------------------+--------------------------------------------------------+
| func.to_date(18875)             | func.subtract_years(func.to_date(18875), 2)            |
+---------------------------------+--------------------------------------------------------+
| 2021-09-05                      | 2019-09-05                                             |
+---------------------------------+--------------------------------------------------------+

func.to_date(18875), func.subtract_quarters(func.to_date(18875), 2)
+---------------------------------+--------------------------------------------------------+
| func.to_date(18875)             | subtract_quarters(func.to_date(18875), 2)              |
+---------------------------------+--------------------------------------------------------+
| 2021-09-05                      | 2021-03-05                                             |
+---------------------------------+--------------------------------------------------------+

func.to_date(18875), func.subtract_months(func.to_date(18875), 2)
+---------------------------------+--------------------------------------------------------+
| func.to_date(18875)             | func.subtract_months(func.to_date(18875), 2)           |
+---------------------------------+--------------------------------------------------------+
| 2021-09-05                      | 2021-07-05                                             |
+---------------------------------+--------------------------------------------------------+

func.to_date(18875), func.subtract_days(func.to_date(18875), 2)
+---------------------------------+--------------------------------------------------------+
| func.to_date(18875)             | func.subtract_days(func.to_date(18875), 2)             |
+---------------------------------+--------------------------------------------------------+
| 2021-09-05                      | 2021-09-03                                             |
+---------------------------------+--------------------------------------------------------+

func.to_datetime(1630833797), func.subtract_hours(func.to_datetime(1630833797), 2)
+---------------------------------+--------------------------------------------------------+
| func.to_datetime(1630833797)    | func.subtract_hours(func.to_datetime(1630833797), 2)   |
+---------------------------------+--------------------------------------------------------+
| 2021-09-05 09:23:17.000000      | 2021-09-05 07:23:17.000000                             |
+---------------------------------+--------------------------------------------------------+

func.to_datetime(1630833797), func.subtract_minutes(func.to_datetime(1630833797), 2)
+---------------------------------+--------------------------------------------------------+
| func.to_datetime(1630833797)    | func.subtract_minutes(func.to_datetime(1630833797), 2) |
+---------------------------------+--------------------------------------------------------+
| 2021-09-05 09:23:17.000000      | 2021-09-05 09:21:17.000000                             |
+---------------------------------+--------------------------------------------------------+

func.to_datetime(1630833797), func.subtract_seconds(func.to_datetime(1630833797), 2)
+---------------------------------+--------------------------------------------------------+
| func.to_datetime(1630833797)    | func.subtract_seconds(func.to_datetime(1630833797), 2) |
+---------------------------------+--------------------------------------------------------+
| 2021-09-05 09:23:17.000000      | 2021-09-05 09:23:15.000000                             |
+---------------------------------+--------------------------------------------------------+

SQL Syntax

SUBTRACT_YEARS(<exp0>, <expr1>)
SUBTRACT_QUARTERS(<exp0>, <expr1>)
SUBTRACT_MONTHS(<exp0>, <expr1>)
SUBTRACT_DAYS(<exp0>, <expr1>)
SUBTRACT_HOURS(<exp0>, <expr1>)
SUBTRACT_MINUTES(<exp0>, <expr1>)
SUBTRACT_SECONDS(<exp0>, <expr1>)

Return Type

DATE, TIMESTAMP depends on the input.

SQL Examples

SELECT to_date(18875), subtract_years(to_date(18875), 2);
+----------------+-----------------------------------+
| to_date(18875) | subtract_years(to_date(18875), 2) |
+----------------+-----------------------------------+
| 2021-09-05     | 2019-09-05                        |
+----------------+-----------------------------------+

SELECT to_date(18875), subtract_quarters(to_date(18875), 2);
+----------------+--------------------------------------+
| to_date(18875) | subtract_quarters(to_date(18875), 2) |
+----------------+--------------------------------------+
| 2021-09-05     | 2021-03-05                           |
+----------------+--------------------------------------+

SELECT to_date(18875), subtract_months(to_date(18875), 2);
+----------------+------------------------------------+
| to_date(18875) | subtract_months(to_date(18875), 2) |
+----------------+------------------------------------+
| 2021-09-05     | 2021-07-05                         |
+----------------+------------------------------------+

SELECT to_date(18875), subtract_days(to_date(18875), 2);
+----------------+----------------------------------+
| to_date(18875) | subtract_days(to_date(18875), 2) |
+----------------+----------------------------------+
| 2021-09-05     | 2021-09-03                       |
+----------------+----------------------------------+

SELECT to_datetime(1630833797), subtract_hours(to_datetime(1630833797), 2);
+----------------------------+--------------------------------------------+
| to_datetime(1630833797)    | subtract_hours(to_datetime(1630833797), 2) |
+----------------------------+--------------------------------------------+
| 2021-09-05 09:23:17.000000 | 2021-09-05 07:23:17.000000                 |
+----------------------------+--------------------------------------------+

SELECT to_datetime(1630833797), subtract_minutes(to_datetime(1630833797), 2);
+----------------------------+----------------------------------------------+
| to_datetime(1630833797)    | subtract_minutes(to_datetime(1630833797), 2) |
+----------------------------+----------------------------------------------+
| 2021-09-05 09:23:17.000000 | 2021-09-05 09:21:17.000000                   |
+----------------------------+----------------------------------------------+

SELECT to_datetime(1630833797), subtract_seconds(to_datetime(1630833797), 2);
+----------------------------+----------------------------------------------+
| to_datetime(1630833797)    | subtract_seconds(to_datetime(1630833797), 2) |
+----------------------------+----------------------------------------------+
| 2021-09-05 09:23:17.000000 | 2021-09-05 09:23:15.000000                   |
+----------------------------+----------------------------------------------+

18 - TIME_SLOT

Rounds the time to the half-hour.

Analyze Syntax

func.time_slot(<expr>)

Analyze Examples

func.time_slot('2023-11-12 09:38:18.165575')
┌───────────────────────────────-───-───-──────┐
│ func.time_slot('2023-11-12 09:38:18.165575') │
│                Timestamp                     │
├─────────────────────────────────-───-────────┤
│ 2023-11-12 09:30:00                          │
└─────────────────────────────────-───-────────┘

SQL Syntax

time_slot(<expr>)

Arguments

Arguments	Description
`<expr>`	timestamp

Return Type

TIMESTAMP, returns in “YYYY-MM-DD hh:mm:ss.ffffff” format.

SQL Examples

SELECT
  time_slot('2023-11-12 09:38:18.165575')

┌─────────────────────────────────────────┐
│ time_slot('2023-11-12 09:38:18.165575') │
│                Timestamp                │
├─────────────────────────────────────────┤
│ 2023-11-12 09:30:00                     │
└─────────────────────────────────────────┘

19 - TIMEZONE

Returns the timezone for the current connection.

PlaidCloud Lakehouse uses UTC (Coordinated Universal Time) as the default timezone and allows you to change the timezone to your current geographic location. For the available values you can assign to the timezone setting, refer to https://docs.rs/chrono-tz/latest/chrono_tz/enum.Tz.html. See the examples below for details.

Analyze Syntax

func.timezone()

Analyze Examples

func.timezone()
┌─────────────────────┐
│ timezone            │
├─────────────────────┤
│ UTC                 │
└─────────────────────┘

SQL Syntax

SELECT TIMEZONE();

SQL Examples

-- Return the current timezone
SELECT TIMEZONE();
+-----------------+
| TIMEZONE('UTC') |
+-----------------+
| UTC             |
+-----------------+

-- Set the timezone to China Standard Time
SET timezone='Asia/Shanghai';

SELECT TIMEZONE();
+---------------------------+
| TIMEZONE('Asia/Shanghai') |
+---------------------------+
| Asia/Shanghai             |
+---------------------------+

20 - TO_DATE

Converts an expression to a date, including:

Converting a timestamp-format string to a date: Extracts a date from the given string.
Converting an integer to a date: Interprets the integer as the number of days before (for negative numbers) or after (for positive numbers) the Unix epoch (midnight on January 1, 1970). Please note that a Date value ranges from 1000-01-01 to 9999-12-31. PlaidCloud Lakehouse would return an error if you run "SELECT TO_DATE(9999999999999999999)".
Converting a string to a date using the specified format: The function takes two arguments, converting the first string to a date based on the format specified in the second string. To customize the date and time format in PlaidCloud Lakehouse, specifiers can be used. For a comprehensive list of supported specifiers, see Formatting Date and Time.

Analyze Syntax

func.to_date('<timestamp_expr>')
func.to_date(<integer>)
func.to_date('<string>', '<format>')

Analyze Examples

func.typeof(func.to_date('2022-01-02')), func.typeof(func.str_to_date('2022-01-02'))

┌───────────────────────────────────────────────────────────────────────────────────────┐
│ func.typeof(func.to_date('2022-01-02')) │ func.typeof(func.str_to_date('2022-01-02')) │
├─────────────────────────────────────────┼─────────────────────────────────────────────┤
│ DATE                                    │ DATE                                        │
└───────────────────────────────────────────────────────────────────────────────────────┘

SQL Syntax

-- Convert a timestamp-format string
TO_DATE('<timestamp_expr>')

-- Convert an integer
TO_DATE(<integer>)

-- Convert a string using the given format
TO_DATE('<string>', '<format>')

Aliases

Return Type

The function returns a date in the format "YYYY-MM-DD":

SELECT TYPEOF(TO_DATE('2022-01-02')), TYPEOF(STR_TO_DATE('2022-01-02'));

┌───────────────────────────────────────────────────────────────────┐
│ typeof(to_date('2022-01-02')) │ typeof(str_to_date('2022-01-02')) │
├───────────────────────────────┼───────────────────────────────────┤
│ DATE                          │ DATE                              │
└───────────────────────────────────────────────────────────────────┘

To convert the returned date back to a string, use the DATE_FORMAT function:

SELECT DATE_FORMAT(TO_DATE('2022-01-02')) AS dt, TYPEOF(dt);

┌─────────────────────────┐
│     dt     │ typeof(dt) │
├────────────┼────────────┤
│ 2022-01-02 │ VARCHAR    │
└─────────────────────────┘

SQL Examples

SQL Examples 1: Converting a Timestamp-Format String

SELECT TO_DATE('2022-01-02T01:12:00+07:00'), STR_TO_DATE('2022-01-02T01:12:00+07:00');

┌─────────────────────────────────────────────────────────────────────────────────┐
│ to_date('2022-01-02t01:12:00+07:00') │ str_to_date('2022-01-02t01:12:00+07:00') │
├──────────────────────────────────────┼──────────────────────────────────────────┤
│ 2022-01-01                           │ 2022-01-01                               │
└─────────────────────────────────────────────────────────────────────────────────┘

SELECT TO_DATE('2022-01-02'), STR_TO_DATE('2022-01-02');

┌───────────────────────────────────────────────────┐
│ to_date('2022-01-02') │ str_to_date('2022-01-02') │
├───────────────────────┼───────────────────────────┤
│ 2022-01-02            │ 2022-01-02                │
└───────────────────────────────────────────────────┘

SQL Examples 2: Converting an Integer

SELECT TO_DATE(1), STR_TO_DATE(1), TO_DATE(-1), STR_TO_DATE(-1);

┌───────────────────────────────────────────────────────────────────┐
│ to_date(1) │ str_to_date(1) │ to_date((- 1)) │ str_to_date((- 1)) │
│    Date    │      Date      │      Date      │        Date        │
├────────────┼────────────────┼────────────────┼────────────────────┤
│ 1970-01-02 │ 1970-01-02     │ 1969-12-31     │ 1969-12-31         │
└───────────────────────────────────────────────────────────────────┘

SQL Examples 3: Converting a String using the Given Format

SELECT TO_DATE('12/25/2022','%m/%d/%Y'), STR_TO_DATE('12/25/2022','%m/%d/%Y');

┌───────────────────────────────────────────────────────────────────────────┐
│ to_date('12/25/2022', '%m/%d/%y') │ str_to_date('12/25/2022', '%m/%d/%y') │
├───────────────────────────────────┼───────────────────────────────────────┤
│ 2022-12-25                        │ 2022-12-25                            │
└───────────────────────────────────────────────────────────────────────────┘

21 - TO_DATETIME

Alias for TO_TIMESTAMP.

22 - TO_DAY_OF_MONTH

Convert a date or date with time (timestamp/datetime) to a UInt8 number containing the number of the day of the month (1-31).

Analyze Syntax

func.to_day_of_month(<expr>)

Analyze Examples

func.now(), func.to_day_of_month(func.now()), func.day(func.now())

┌──────────────────────────────────────────────────────────────────────────────────────┐
│       func.now()           │ func.to_day_of_month(func.now()) │ func.day(func.now()) │
├────────────────────────────┼──────────────────────────────────┼──────────────────────┤
│ 2024-03-14 23:35:41.947962 │                               14 │                   14 │
└──────────────────────────────────────────────────────────────────────────────────────┘

SQL Syntax

TO_DAY_OF_MONTH(<expr>)

Arguments

Arguments	Description
`<expr>`	date/timestamp

Aliases

Return Type

TINYINT

SQL Examples

SELECT NOW(), TO_DAY_OF_MONTH(NOW()), DAY(NOW());

┌──────────────────────────────────────────────────────────────────┐
│            now()           │ to_day_of_month(now()) │ day(now()) │
├────────────────────────────┼────────────────────────┼────────────┤
│ 2024-03-14 23:35:41.947962 │                     14 │         14 │
└──────────────────────────────────────────────────────────────────┘

23 - TO_DAY_OF_WEEK

Converts a date or date with time (timestamp/datetime) to a UInt8 number containing the number of the day of the week (Monday is 1, and Sunday is 7).

Analyze Syntax

func.to_day_of_week(<expr>)

Analyze Examples

func.to_day_of_week('2023-11-12 09:38:18.165575')

┌────────────────────────────────────────────────────┐
│ func.to_day_of_week('2023-11-12 09:38:18.165575')  │
│                     UInt8                          │
├────────────────────────────────────────────────────┤
│                                                  7 │
└────────────────────────────────────────────────────┘

SQL Syntax

TO_DAY_OF_WEEK(<expr>)

Arguments

Arguments	Description
`<expr>`	date/timestamp

Return Type

``TINYINT`

SQL Examples


SELECT
    to_day_of_week('2023-11-12 09:38:18.165575')

┌──────────────────────────────────────────────┐
│ to_day_of_week('2023-11-12 09:38:18.165575') │
│                     UInt8                    │
├──────────────────────────────────────────────┤
│                                            7 │
└──────────────────────────────────────────────┘

24 - TO_DAY_OF_YEAR

Convert a date or date with time (timestamp/datetime) to a UInt16 number containing the number of the day of the year (1-366).

Analyze Syntax

func.to_day_of_year(<expr>)

Analyze Examples

func.to_day_of_week('2023-11-12 09:38:18.165575')

┌────────────────────────────────────────────────────┐
│ func.to_day_of_year('2023-11-12 09:38:18.165575')  │
│                     UInt8                          │
├────────────────────────────────────────────────────┤
│                                                316 │
└────────────────────────────────────────────────────┘

SQL Syntax

TO_DAY_OF_YEAR(<expr>)

Arguments

Arguments	Description
`<expr>`	date/timestamp

Return Type

SMALLINT

SQL Examples

SELECT
    to_day_of_year('2023-11-12 09:38:18.165575')

┌──────────────────────────────────────────────┐
│ to_day_of_year('2023-11-12 09:38:18.165575') │
│                    UInt16                    │
├──────────────────────────────────────────────┤
│                                          316 │
└──────────────────────────────────────────────┘

25 - TO_HOUR

Converts a date with time (timestamp/datetime) to a UInt8 number containing the number of the hour in 24-hour time (0-23). This function assumes that if clocks are moved ahead, it is by one hour and occurs at 2 a.m., and if clocks are moved back, it is by one hour and occurs at 3 a.m. (which is not always true – even in Moscow the clocks were twice changed at a different time).

Analyze Syntax

func.to_hour(<expr>)

Analyze Examples

func.to_hour('2023-11-12 09:38:18.165575')

┌────────────────────────────────────────────────────┐
│ func.to_hour('2023-11-12 09:38:18.165575')         │
│                     UInt8                          │
├────────────────────────────────────────────────────┤
│                                                  9 │
└────────────────────────────────────────────────────┘

SQL Syntax

TO_HOUR(<expr>)

Arguments

Arguments	Description
`<expr>`	timestamp

Return Type

TINYINT

SQL Examples

SELECT
    to_hour('2023-11-12 09:38:18.165575')

┌───────────────────────────────────────┐
│ to_hour('2023-11-12 09:38:18.165575') │
│                 UInt8                 │
├───────────────────────────────────────┤
│                                     9 │
└───────────────────────────────────────┘

26 - TO_MINUTE

Converts a date with time (timestamp/datetime) to a UInt8 number containing the number of the minute of the hour (0-59).

Analyze Syntax

func.to_minute(<expr>)

Analyze Examples

func.to_minute('2023-11-12 09:38:18.165575')

┌────────────────────────────────────────────────────┐
│ func.to_minute('2023-11-12 09:38:18.165575')       │
│                     UInt8                          │
├────────────────────────────────────────────────────┤
│                                                 38 │
└────────────────────────────────────────────────────┘

SQL Syntax

TO_MINUTE(<expr>)

Arguments

Arguments	Description
`<expr>`	timestamp

Return Type

TINYINT

SQL Examples

SELECT
    to_minute('2023-11-12 09:38:18.165575')

┌─────────────────────────────────────────┐
│ to_minute('2023-11-12 09:38:18.165575') │
│                  UInt8                  │
├─────────────────────────────────────────┤
│                                      38 │
└─────────────────────────────────────────┘

27 - TO_MONDAY

Round down a date or date with time (timestamp/datetime) to the nearest Monday. Returns the date.

Analyze Syntax

func.to_monday(<expr>)

Analyze Examples

func.to_monday('2023-11-12 09:38:18.165575')

┌────────────────────────────────────────────────────┐
│ func.to_monday('2023-11-12 09:38:18.165575')       │
│                      Date                          │
├────────────────────────────────────────────────────┤
│ 2023-11-06                                         │
└────────────────────────────────────────────────────┘

SQL Syntax

TO_MONDAY(<expr>)

Arguments

Arguments	Description
`<expr>`	date/timestamp

Return Type

DATE, returns date in “YYYY-MM-DD” format.

SQL Examples

SELECT
    to_monday('2023-11-12 09:38:18.165575')

┌─────────────────────────────────────────┐
│ to_monday('2023-11-12 09:38:18.165575') │
│                   Date                  │
├─────────────────────────────────────────┤
│ 2023-11-06                              │
└─────────────────────────────────────────┘

28 - TO_MONTH

Convert a date or date with time (timestamp/datetime) to a UInt8 number containing the month number (1-12).

Analyze Syntax

func.to_month(<expr>)

Analyze Examples

func.now(), func.to_month(func.now()), func.month(func.now())

┌─────────────────────────────────────────────────────────────────────────────────┐
│       func.now()           │ func.to_month(func.now()) │ func.month(func.now()) │
├────────────────────────────┼───────────────────────────┼────────────────────────┤
│ 2024-03-14 23:34:02.161291 │                         3 │                      3 │
└─────────────────────────────────────────────────────────────────────────────────┘

SQL Syntax

TO_MONTH(<expr>)

Arguments

Arguments	Description
`<expr>`	date/timestamp

Aliases

MONTH

Return Type

TINYINT

SQL Examples

SELECT NOW(), TO_MONTH(NOW()), MONTH(NOW());

┌─────────────────────────────────────────────────────────────┐
│            now()           │ to_month(now()) │ month(now()) │
├────────────────────────────┼─────────────────┼──────────────┤
│ 2024-03-14 23:34:02.161291 │               3 │            3 │
└─────────────────────────────────────────────────────────────┘

29 - TO_QUARTER

Retrieves the quarter (1, 2, 3, or 4) from a given date or timestamp.

Analyze Syntax

func.to_quarter(<expr>)

Analyze Examples

func.now(), func.to_quarter(func.now()), func.quarter(func.now())

┌─────────────────────────────────────────────────────────────────────────────────────┐
│       func.now()           │ func.to_quarter(func.now()) │ func.quarter(func.now()) │
├────────────────────────────┼─────────────────────────────┼──────────────────────────┤
│ 2024-03-14 23:32:52.743133 │                           3 │                        3 │
└─────────────────────────────────────────────────────────────────────────────────────┘

SQL Syntax

TO_QUARTER( <date_or_time_expr> )

Aliases

QUARTER

Return Type

Integer.

SQL Examples

SELECT NOW(), TO_QUARTER(NOW()), QUARTER(NOW());

┌─────────────────────────────────────────────────────────────────┐
│            now()           │ to_quarter(now()) │ quarter(now()) │
├────────────────────────────┼───────────────────┼────────────────┤
│ 2024-03-14 23:32:52.743133 │                 1 │              1 │
└─────────────────────────────────────────────────────────────────┘

30 - TO_SECOND

Converts a date with time (timestamp/datetime) to a UInt8 number containing the number of the second in the minute (0-59).

Analyze Syntax

func.to_second(<expr>)

Analyze Examples

func.to_second('2023-11-12 09:38:18.165575')

┌──────────────────────────────────────────────┐
│ func.to_second('2023-11-12 09:38:18.165575') │
│                   UInt8                      │
├──────────────────────────────────────────────┤
│                                           18 │
└──────────────────────────────────────────────┘

SQL Syntax

TO_SECOND(<expr>)

Arguments

Arguments	Description
`<expr>`	timestamp

Return Type

TINYINT

SQL Examples

SELECT
    to_second('2023-11-12 09:38:18.165575')

┌─────────────────────────────────────────┐
│ to_second('2023-11-12 09:38:18.165575') │
│                  UInt8                  │
├─────────────────────────────────────────┤
│                                      18 │
└─────────────────────────────────────────┘

31 - TO_START_OF_DAY

Rounds down a date with time (timestamp/datetime) to the start of the day.

Analyze Syntax

func.to_start_of_day(<expr>)

Analyze Examples

func.to_start_of_day('2023-11-12 09:38:18.165575')

┌────────────────────────────────────────────────────┐
│ func.to_start_of_day('2023-11-12 09:38:18.165575') │
│                     Timestamp                      │
├────────────────────────────────────────────────────┤
│ 2023-11-12 00:00:00                                │
└────────────────────────────────────────────────────┘

SQL Syntax

TO_START_OF_DAY( <expr> )

Arguments

Arguments	Description
`<expr>`	timestamp

Return Type

TIMESTAMP, returns date in “YYYY-MM-DD hh:mm:ss.ffffff” format.

SQL Examples

SELECT
  to_start_of_day('2023-11-12 09:38:18.165575')

┌───────────────────────────────────────────────┐
│ to_start_of_day('2023-11-12 09:38:18.165575') │
│                   Timestamp                   │
├───────────────────────────────────────────────┤
│ 2023-11-12 00:00:00                           │
└───────────────────────────────────────────────┘

32 - TO_START_OF_FIFTEEN_MINUTES

Rounds down the date with time (timestamp/datetime) to the start of the fifteen-minute interval.

Analyze Syntax

func.to_start_of_fifteen_minutes(<expr>)

Analyze Examples

func.to_start_of_fifteen_minutes('2023-11-12 09:38:18.165575')

┌────────────────────────────────────────────────────────────────┐
│ func.to_start_of_fifteen_minutes('2023-11-12 09:38:18.165575') │
│                          Timestamp                             │
├────────────────────────────────────────────────────────────────┤
│ 2023-11-12 09:30:00                                            │
└────────────────────────────────────────────────────────────────┘

SQL Syntax

TO_START_OF_FIFTEEN_MINUTES(<expr>)

Arguments

Arguments	Description
`<expr>`	timestamp

Return Type

TIMESTAMP, returns date in “YYYY-MM-DD hh:mm:ss.ffffff” format.

SQL Examples

SELECT
  to_start_of_fifteen_minutes('2023-11-12 09:38:18.165575')

┌───────────────────────────────────────────────────────────┐
│ to_start_of_fifteen_minutes('2023-11-12 09:38:18.165575') │
│                         Timestamp                         │
├───────────────────────────────────────────────────────────┤
│ 2023-11-12 09:30:00                                       │
└───────────────────────────────────────────────────────────┘

33 - TO_START_OF_FIVE_MINUTES

Rounds down a date with time (timestamp/datetime) to the start of the five-minute interval.

Analyze Syntax

func.to_start_of_five_minutes(<expr>)

Analyze Examples

func.to_start_of_five_minutes('2023-11-12 09:38:18.165575')

┌────────────────────────────────────────────────────────────────┐
│ func.to_start_of_five_minutes('2023-11-12 09:38:18.165575')    │
│                          Timestamp                             │
├────────────────────────────────────────────────────────────────┤
│ 2023-11-12 09:35:00                                            │
└────────────────────────────────────────────────────────────────┘

SQL Syntax

TO_START_OF_FIVE_MINUTES(<expr>)

Arguments

Arguments	Description
`<expr>`	timestamp

Return Type

TIMESTAMP, returns date in “YYYY-MM-DD hh:mm:ss.ffffff” format.

SQL Examples

SELECT
  to_start_of_five_minutes('2023-11-12 09:38:18.165575')

┌────────────────────────────────────────────────────────┐
│ to_start_of_five_minutes('2023-11-12 09:38:18.165575') │
│                        Timestamp                       │
├────────────────────────────────────────────────────────┤
│ 2023-11-12 09:35:00                                    │
└────────────────────────────────────────────────────────┘

34 - TO_START_OF_HOUR

Rounds down a date with time (timestamp/datetime) to the start of the hour.

Analyze Syntax

func.to_start_of_hour(<expr>)

Analyze Examples

func.to_start_of_hour('2023-11-12 09:38:18.165575')

┌────────────────────────────────────────────────────────────────┐
│ func.to_start_of_hour('2023-11-12 09:38:18.165575')            │
│                          Timestamp                             │
├────────────────────────────────────────────────────────────────┤
│ 2023-11-12 09:00:00                                            │
└────────────────────────────────────────────────────────────────┘

SQL Syntax

TO_START_OF_HOUR(<expr>)

Arguments

Arguments	Description
`<expr>`	timestamp

Return Type

TIMESTAMP, returns date in “YYYY-MM-DD hh:mm:ss.ffffff” format.

SQL Examples

SELECT
  to_start_of_hour('2023-11-12 09:38:18.165575')

┌────────────────────────────────────────────────┐
│ to_start_of_hour('2023-11-12 09:38:18.165575') │
│                    Timestamp                   │
├────────────────────────────────────────────────┤
│ 2023-11-12 09:00:00                            │
└────────────────────────────────────────────────┘

35 - TO_START_OF_ISO_YEAR

Returns the first day of the ISO year for a date or a date with time (timestamp/datetime).

Analyze Syntax

func.to_start_of_iso_year(<expr>)

Analyze Examples

func.to_start_of_iso_year('2023-11-12 09:38:18.165575')

┌────────────────────────────────────────────────────────────────┐
│ func.to_start_of_iso_year('2023-11-12 09:38:18.165575')        │
│                          Date                                  │
├────────────────────────────────────────────────────────────────┤
│ 2023-01-02                                                     │
└────────────────────────────────────────────────────────────────┘

SQL Syntax

TO_START_OF_ISO_YEAR(<expr>)

Arguments

Arguments	Description
`<expr>`	date/timestamp

Return Type

DATE, returns date in “YYYY-MM-DD” format.

SQL Examples

SELECT
  to_start_of_iso_year('2023-11-12 09:38:18.165575')

┌────────────────────────────────────────────────────┐
│ to_start_of_iso_year('2023-11-12 09:38:18.165575') │
│                        Date                        │
├────────────────────────────────────────────────────┤
│ 2023-01-02                                         │
└────────────────────────────────────────────────────┘

36 - TO_START_OF_MINUTE

Rounds down a date with time (timestamp/datetime) to the start of the minute.

Analyze Syntax

func.to_start_of_minute(<expr>)

Analyze Examples

func.to_start_of_minute('2023-11-12 09:38:18.165575')

┌────────────────────────────────────────────────────────────────┐
│ func.to_start_of_minute('2023-11-12 09:38:18.165575')          │
│                          Timestamp                             │
├────────────────────────────────────────────────────────────────┤
│ 2023-11-12 09:38:00                                            │
└────────────────────────────────────────────────────────────────┘

SQL Syntax

TO_START_OF_MINUTE( <expr> )

Arguments

Arguments	Description
`<expr>`	timestamp

Return Type

TIMESTAMP, returns date in “YYYY-MM-DD hh:mm:ss.ffffff” format.

SQL Examples

SELECT
  to_start_of_minute('2023-11-12 09:38:18.165575')

┌──────────────────────────────────────────────────┐
│ to_start_of_minute('2023-11-12 09:38:18.165575') │
│                     Timestamp                    │
├──────────────────────────────────────────────────┤
│ 2023-11-12 09:38:00                              │
└──────────────────────────────────────────────────┘

37 - TO_START_OF_MONTH

Rounds down a date or date with time (timestamp/datetime) to the first day of the month. Returns the date.

Analyze Syntax

func.to_start_of_month(<expr>)

Analyze Examples

func.to_start_of_month('2023-11-12 09:38:18.165575')

┌────────────────────────────────────────────────────────────────┐
│ func.to_start_of_month('2023-11-12 09:38:18.165575')           │
│                          Date                                  │
├────────────────────────────────────────────────────────────────┤
│ 2023-11-01                                                     │
└────────────────────────────────────────────────────────────────┘

SQL Syntax

TO_START_OF_MONTH(<expr>)

Arguments

Arguments	Description
`<expr>`	date/timestamp

Return Type

DATE, returns date in “YYYY-MM-DD” format.

SQL Examples

SELECT
  to_start_of_month('2023-11-12 09:38:18.165575')

┌─────────────────────────────────────────────────┐
│ to_start_of_month('2023-11-12 09:38:18.165575') │
│                       Date                      │
├─────────────────────────────────────────────────┤
│ 2023-11-01                                      │
└─────────────────────────────────────────────────┘

38 - TO_START_OF_QUARTER

Rounds down a date or date with time (timestamp/datetime) to the first day of the quarter. The first day of the quarter is either 1 January, 1 April, 1 July, or 1 October. Returns the date.

Analyze Syntax

func.to_start_of_quarter(<expr>)

Analyze Examples

func.to_start_of_quarter('2023-11-12 09:38:18.165575')

┌────────────────────────────────────────────────────────────────┐
│ func.to_start_of_quarter('2023-11-12 09:38:18.165575')         │
│                          Date                                  │
├────────────────────────────────────────────────────────────────┤
│ 2023-10-01                                                     │
└────────────────────────────────────────────────────────────────┘

SQL Syntax

TO_START_OF_QUARTER(<expr>)

Arguments

Arguments	Description
`<expr>`	date/timestamp

Return Type

DATE, returns date in “YYYY-MM-DD” format.

SQL Examples

SELECT
  to_start_of_quarter('2023-11-12 09:38:18.165575')

┌───────────────────────────────────────────────────┐
│ to_start_of_quarter('2023-11-12 09:38:18.165575') │
│                        Date                       │
├───────────────────────────────────────────────────┤
│ 2023-10-01                                        │
└───────────────────────────────────────────────────┘

39 - TO_START_OF_SECOND

Rounds down a date with time (timestamp/datetime) to the start of the second.

Analyze Syntax

func.to_start_of_second(<expr>)

Analyze Examples

func.to_start_of_second('2023-11-12 09:38:18.165575')

┌────────────────────────────────────────────────────────────────┐
│ func.to_start_of_second('2023-11-12 09:38:18.165575')          │
│                          Timestamp                             │
├────────────────────────────────────────────────────────────────┤
│ 2023-11-12 09:38:18                                            │
└────────────────────────────────────────────────────────────────┘

SQL Syntax

TO_START_OF_SECOND(<expr>)

Arguments

Arguments	Description
`<expr>`	timestamp

Return Type

TIMESTAMP, returns date in “YYYY-MM-DD hh:mm:ss.ffffff” format.

SQL Examples

SELECT
  to_start_of_second('2023-11-12 09:38:18.165575')

┌──────────────────────────────────────────────────┐
│ to_start_of_second('2023-11-12 09:38:18.165575') │
│                     Timestamp                    │
├──────────────────────────────────────────────────┤
│ 2023-11-12 09:38:18                              │
└──────────────────────────────────────────────────┘

40 - TO_START_OF_TEN_MINUTES

Rounds down a date with time (timestamp/datetime) to the start of the ten-minute interval.

Analyze Syntax

func.to_start_of_ten_minutes(<expr>)

Analyze Examples

func.to_start_of_ten_minutes('2023-11-12 09:38:18.165575')

┌────────────────────────────────────────────────────────────────┐
│ func.to_start_of_ten_minutes('2023-11-12 09:38:18.165575')     │
│                          Timestamp                             │
├────────────────────────────────────────────────────────────────┤
│ 2023-11-12 09:30:00                                            │
└────────────────────────────────────────────────────────────────┘

SQL Syntax

TO_START_OF_TEN_MINUTES(<expr>)

Arguments

Arguments	Description
`<expr>`	timestamp

Return Type

TIMESTAMP, returns date in “YYYY-MM-DD hh:mm:ss.ffffff” format.

SQL Examples

SELECT
  to_start_of_ten_minutes('2023-11-12 09:38:18.165575')

┌───────────────────────────────────────────────────────┐
│ to_start_of_ten_minutes('2023-11-12 09:38:18.165575') │
│                       Timestamp                       │
├───────────────────────────────────────────────────────┤
│ 2023-11-12 09:30:00                                   │
└───────────────────────────────────────────────────────┘

41 - TO_START_OF_WEEK

Returns the first day of the week for a date or a date with time (timestamp/datetime). The first day of a week can be Sunday or Monday, which is specified by the argument mode.

Analyze Syntax

func.to_start_of_week(<expr>)

Analyze Examples

func.to_start_of_week('2023-11-12 09:38:18.165575')

┌────────────────────────────────────────────────────────────────┐
│ func.to_start_of_week('2023-11-12 09:38:18.165575')            │
│                          Date                                  │
├────────────────────────────────────────────────────────────────┤
│ 2023-11-12                                                     │
└────────────────────────────────────────────────────────────────┘

SQL Syntax

TO_START_OF_WEEK(<expr> [, mode])

Arguments

Arguments	Description
`<expr>`	date/timestamp
`[mode]`	Optional. If it is 0, the result is Sunday, otherwise, the result is Monday. The default value is 0

Return Type

DATE, returns date in “YYYY-MM-DD” format.

SQL Examples

SELECT
  to_start_of_week('2023-11-12 09:38:18.165575')

┌────────────────────────────────────────────────┐
│ to_start_of_week('2023-11-12 09:38:18.165575') │
│                      Date                      │
├────────────────────────────────────────────────┤
│ 2023-11-12                                     │
└────────────────────────────────────────────────┘

42 - TO_START_OF_YEAR

Returns the first day of the year for a date or a date with time (timestamp/datetime).

Analyze Syntax

func.to_start_of_year(<expr>)

Analyze Examples

func.to_start_of_year('2023-11-12 09:38:18.165575')

┌────────────────────────────────────────────────────────────────┐
│ func.to_start_of_year('2023-11-12 09:38:18.165575')            │
│                          Date                                  │
├────────────────────────────────────────────────────────────────┤
│ 2023-01-01                                                     │
└────────────────────────────────────────────────────────────────┘

SQL Syntax

TO_START_OF_YEAR(<expr>)

Arguments

Arguments	Description
`<expr>`	date/timestamp

Return Type

DATE, returns date in “YYYY-MM-DD” format.

SQL Examples

SELECT
  to_start_of_year('2023-11-12 09:38:18.165575')

┌────────────────────────────────────────────────┐
│ to_start_of_year('2023-11-12 09:38:18.165575') │
│                      Date                      │
├────────────────────────────────────────────────┤
│ 2023-01-01                                     │
└────────────────────────────────────────────────┘

43 - TO_TIMESTAMP

TO_TIMESTAMP converts an expression to a date with time (timestamp/datetime).

The function can accept one or two arguments. If given one argument, the function extracts a date from the string. If the argument is an integer, the function interprets the integer as the number of seconds, milliseconds, or microseconds before (for a negative number) or after (for a positive number) the Unix epoch (midnight on January 1, 1970):

If the integer is less than 31,536,000,000, it is treated as seconds.
If the integer is greater than or equal to 31,536,000,000 and less than 31,536,000,000,000, it is treated as milliseconds.
If the integer is greater than or equal to 31,536,000,000,000, it is treated as microseconds.

If given two arguments, the function converts the first string to a timestamp based on the format specified in the second string. To customize the format of date and time in PlaidCloud Lakehouse, you can utilize specifiers. These specifiers allow you to define the desired format for date and time values. For a comprehensive list of supported specifiers, see Formatting Date and Time.

The output timestamp reflects your PlaidCloud Lakehouse timezone.
The timezone information must be included in the string you want to convert, otherwise NULL will be returned.

Analyze Syntax

func.to_timestamp(<expr>)

Analyze Examples

func.to_timestamp('2022-01-02T03:25:02.868894-07:00')

┌────────────────────────────────────────────────────────────────┐
│ func.to_timestamp('2022-01-02T03:25:02.868894-07:00')          │
│                          Timestamp                             │
├────────────────────────────────────────────────────────────────┤
│ 2022-01-02 10:25:02.868894                                     │
└────────────────────────────────────────────────────────────────┘

SQL Syntax

-- Convert a string or integer to a timestamp
TO_TIMESTAMP(<expr>)

-- Convert a string to a timestamp using the given pattern
TO_TIMESTAMP(<expr, expr>)

Return Type

Returns a timestamp in the format "YYYY-MM-DD hh:mm:ss.ffffff". If the given string matches this format but does not have the time part, it is automatically extended to this pattern. The padding value is 0.

Aliases

SQL Examples

Given a String Argument

SELECT TO_TIMESTAMP('2022-01-02T03:25:02.868894-07:00');

---
2022-01-02 10:25:02.868894

SELECT TO_TIMESTAMP('2022-01-02 02:00:11');

---
2022-01-02 02:00:11.000000

SELECT TO_TIMESTAMP('2022-01-02T02:00:22');

---
2022-01-02 02:00:22.000000

SELECT TO_TIMESTAMP('2022-01-02T01:12:00-07:00');

---
2022-01-02 08:12:00.000000

SELECT TO_TIMESTAMP('2022-01-02T01');

---
2022-01-02 01:00:00.000000

Given an Integer Argument

SELECT TO_TIMESTAMP(1);

---
1970-01-01 00:00:01.000000

SELECT TO_TIMESTAMP(-1);

---
1969-12-31 23:59:59.000000

Note:

Please note that a Timestamp value ranges from 1000-01-01 00:00:00.000000 to 9999-12-31 23:59:59.999999. PlaidCloud Lakehouse would return an error if you run the following statement:

SELECT TO_TIMESTAMP(9999999999999999999);

Given Two Arguments

SET GLOBAL timezone ='Japan';
SELECT TO_TIMESTAMP('2022 年 2 月 4 日、8 時 58 分 59 秒、タイムゾーン：+0900', '%Y年%m月%d日、%H時%M分%S秒、タイムゾーン：%z');

---
2022-02-04 08:58:59.000000

SET GLOBAL timezone ='America/Toronto';
SELECT TO_TIMESTAMP('2022 年 2 月 4 日、8 時 58 分 59 秒、タイムゾーン：+0900', '%Y年%m月%d日、%H時%M分%S秒、タイムゾーン：%z');

---
2022-02-03 18:58:59.000000

44 - TO_UNIX_TIMESTAMP

Converts a timestamp in a date/time format to a Unix timestamp format. A Unix timestamp represents the number of seconds that have elapsed since January 1, 1970, at 00:00:00 UTC.

Analyze Syntax

func.to_unix_timestamp(<expr>)

Analyze Examples

func.to_unix_timestamp('2023-11-12 09:38:18.165575')

┌────────────────────────────────────────────────────────────────┐
│ func.to_unix_timestamp('2023-11-12 09:38:18.165575')           │
│                          UInt32                                │
├────────────────────────────────────────────────────────────────┤
│                                                     1699781898 │
└────────────────────────────────────────────────────────────────┘

SQL Syntax

TO_UNIX_TIMESTAMP(<expr>)

Arguments

Arguments	Description
`<expr>`	Timestamp

For more information about the timestamp data type, see Date & Time.

Return Type

BIGINT

SQL Examples

SELECT
  to_unix_timestamp('2023-11-12 09:38:18.165575')

┌─────────────────────────────────────────────────┐
│ to_unix_timestamp('2023-11-12 09:38:18.165575') │
│                      UInt32                     │
├─────────────────────────────────────────────────┤
│                                      1699781898 │
└─────────────────────────────────────────────────┘

45 - TO_WEEK_OF_YEAR

Calculates the week number within a year for a given date.

ISO week numbering works as follows: January 4th is always considered part of the first week. If January 1st is a Thursday, then the week that spans from Monday, December 29th, to Sunday, January 4th, is designated as ISO week 1. If January 1st falls on a Friday, then the week that goes from Monday, January 4th, to Sunday, January 10th, is marked as ISO week 1.

Analyze Syntax

func.to_week_of_year(<expr>)

Analyze Examples

func.now(), func.to_week_of_year(func.now()), func.week(func.now()), func.weekofyear(func.now())

┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│       func.now()           │ func.to_week_of_year(func.now()) │ func.week(func.now()) │ func.weekofyear(func.now()) │
├────────────────────────────┼──────────────────────────────────┼───────────────────────┼─────────────────────────────┤
│ 2024-03-14 23:30:04.011624 │                               11 │                    11 │                          11 │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

SQL Syntax

TO_WEEK_OF_YEAR(<expr>)

Arguments

Arguments	Description
`<expr>`	date/timestamp

Aliases

Return Type

Returns an integer that represents the week number within a year, with numbering ranging from 1 to 53.

SQL Examples

SELECT NOW(), TO_WEEK_OF_YEAR(NOW()), WEEK(NOW()), WEEKOFYEAR(NOW());

┌───────────────────────────────────────────────────────────────────────────────────────┐
│            now()           │ to_week_of_year(now()) │ week(now()) │ weekofyear(now()) │
├────────────────────────────┼────────────────────────┼─────────────┼───────────────────┤
│ 2024-03-14 23:30:04.011624 │                     11 │          11 │                11 │
└───────────────────────────────────────────────────────────────────────────────────────┘

46 - TO_YEAR

Converts a date or date with time (timestamp/datetime) to a UInt16 number containing the year number (AD).

Analyze Syntax

func.to_year(<expr>)

Analyze Examples

func.now(), func.to_year(func.now()), func.year(func.now())

┌───────────────────────────────────────────────────────────────────────────────┐
│       func.now()           │ func.to_year(func.now()) │ func.year(func.now()) │
├────────────────────────────┼──────────────────────────┼───────────────────────┤
│ 2024-03-14 23:37:03.895166 │                     2024 │                  2024 │
└───────────────────────────────────────────────────────────────────────────────┘

SQL Syntax

TO_YEAR(<expr>)

Arguments

Arguments	Description
`<expr>`	date/timestamp

Aliases

YEAR

Return Type

SMALLINT

SQL Examples

SELECT NOW(), TO_YEAR(NOW()), YEAR(NOW());

┌───────────────────────────────────────────────────────────┐
│            now()           │ to_year(now()) │ year(now()) │
├────────────────────────────┼────────────────┼─────────────┤
│ 2024-03-14 23:37:03.895166 │           2024 │        2024 │
└───────────────────────────────────────────────────────────┘

47 - TO_YYYYMM

Converts a date or date with time (timestamp/datetime) to a UInt32 number containing the year and month number.

Analyze Syntax

func.to_yyyymm(<expr>)

Analyze Examples

func.to_yyyymm('2023-11-12 09:38:18.165575')

┌──────────────────────────────────────────────┐
│ func.to_yyyymm('2023-11-12 09:38:18.165575') │
│                   UInt32                     │
├──────────────────────────────────────────────┤
│                                       202311 │
└──────────────────────────────────────────────┘

SQL Syntax

TO_YYYYMM(<expr>)

Arguments

Arguments	Description
`<expr>`	date/timestamp

Return Type

INT, returns in YYYYMM format.

SQL Examples

SELECT
  to_yyyymm('2023-11-12 09:38:18.165575')

┌─────────────────────────────────────────┐
│ to_yyyymm('2023-11-12 09:38:18.165575') │
│                  UInt32                 │
├─────────────────────────────────────────┤
│                                  202311 │
└─────────────────────────────────────────┘

48 - TO_YYYYMMDD

Converts a date or date with time (timestamp/datetime) to a UInt32 number containing the year and month number (YYYY * 10000 + MM * 100 + DD).

Analyze Syntax

func.to_yyyymmdd(<expr>)

Analyze Examples

func.to_yyyymmdd('2023-11-12 09:38:18.165575')

┌────────────────────────────────────────────────┐
│ func.to_yyyymmdd('2023-11-12 09:38:18.165575') │
│                   UInt32                       │
├────────────────────────────────────────────────┤
│                                       20231112 │
└────────────────────────────────────────────────┘

SQL Syntax

TO_YYYYMMDD(<expr>)

Arguments

Arguments	Description
`<expr>`	date/datetime

Return Type

INT, returns in YYYYMMDD format.

SQL Examples

SELECT
  to_yyyymmdd('2023-11-12 09:38:18.165575')

┌───────────────────────────────────────────┐
│ to_yyyymmdd('2023-11-12 09:38:18.165575') │
│                   UInt32                  │
├───────────────────────────────────────────┤
│                                  20231112 │
└───────────────────────────────────────────┘

49 - TO_YYYYMMDDHH

Formats a given date or timestamp into a string representation in the format "YYYYMMDDHH" (Year, Month, Day, Hour).

Analyze Syntax

func.to_yyyymmddhh(<expr>)

Analyze Examples

func.to_yyyymmddhh('2023-11-12 09:38:18.165575')

┌──────────────────────────────────────────────────┐
│ func.to_yyyymmddhh('2023-11-12 09:38:18.165575') │
│                   UInt32                         │
├──────────────────────────────────────────────────┤
│                                       2023111209 │
└──────────────────────────────────────────────────┘

SQL Syntax

TO_YYYYMMDDHH(<expr>)

Arguments

Arguments	Description
`<expr>`	date/datetime

Return Type

Returns an unsigned 64-bit integer (UInt64) in the format "YYYYMMDDHH".

SQL Examples

SELECT
  to_yyyymmddhh('2023-11-12 09:38:18.165575')

┌─────────────────────────────────────────────┐
│ to_yyyymmddhh('2023-11-12 09:38:18.165575') │
│                    UInt32                   │
├─────────────────────────────────────────────┤
│                                  2023111209 │
└─────────────────────────────────────────────┘

50 - TO_YYYYMMDDHHMMSS

Convert a date or date with time (timestamp/datetime) to a UInt64 number containing the year and month number (YYYY * 10000000000 + MM * 100000000 + DD * 1000000 + hh * 10000 + mm * 100 + ss).

Analyze Syntax

func.to_yyyymmddhhmmss(<expr>)

Analyze Examples

func.to_yyyymmddhhmmss('2023-11-12 09:38:18.165575')

┌──────────────────────────────────────────────────────┐
│ func.to_yyyymmddhhmmss('2023-11-12 09:38:18.165575') │
│                   UInt64                             │
├──────────────────────────────────────────────────────┤
│                                       20231112092818 │
└──────────────────────────────────────────────────────┘

SQL Syntax

TO_YYYYMMDDHHMMSS(<expr>)

Arguments

Arguments	Description
`<expr>`	date/timestamp

Return Type

BIGINT, returns in YYYYMMDDhhmmss format.

SQL Examples

SELECT
  to_yyyymmddhhmmss('2023-11-12 09:38:18.165575')

┌─────────────────────────────────────────────────┐
│ to_yyyymmddhhmmss('2023-11-12 09:38:18.165575') │
│                      UInt64                     │
├─────────────────────────────────────────────────┤
│                                  20231112092818 │
└─────────────────────────────────────────────────┘

51 - TODAY

Returns current date.

Analyze Syntax

func.today()

Analyze Examples

func.today()
+--------------+
| func.today() |
+--------------+
| 2021-09-03   |
+--------------+

SQL Syntax

TODAY()

Return Type

DATE, returns date in “YYYY-MM-DD” format.

SQL Examples

SELECT TODAY();
+------------+
| TODAY()    |
+------------+
| 2021-09-03 |
+------------+

52 - TOMORROW

Returns tomorrow date, same as today() + 1.

Analyze Syntax

func.tomorrow()

Analyze Examples

func.tomorrow()
+-----------------+
| func.tomorrow() |
+-----------------+
| 2021-09-03      |
+-----------------+

SQL Syntax

TOMORROW()

Return Type

DATE, returns date in “YYYY-MM-DD” format.

SQL Examples

SELECT TOMORROW();
+------------+
| TOMORROW() |
+------------+
| 2021-09-04 |
+------------+

SELECT TODAY()+1;
+---------------+
| (TODAY() + 1) |
+---------------+
| 2021-09-04    |
+---------------+

53 - TRY_TO_DATETIME

Alias for TRY_TO_TIMESTAMP.

54 - TRY_TO_TIMESTAMP

A variant of TO_TIMESTAMP in PlaidCloud Lakehouse that, while performing the same conversion of an input expression to a timestamp, incorporates error-handling support by returning NULL if the conversion fails instead of raising an error.

Analyze Syntax

func.try_to_timestamp(<expr>)

Analyze Examples

func.try_to_timestamp('2022-01-02 02:00:11'), func.try_to_datetime('2022-01-02 02:00:11'), func.try_to_timestamp('plaidcloud')
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ func.try_to_timestamp('2022-01-02 02:00:11') │ func.try_to_datetime('2022-01-02 02:00:11') │ func.try_to_timestamp('plaidcloud') │
│                Timestamp                │                Timestamp                         │                                     │
├─────────────────────────────────────────┼──────────────────────────────────────────────────┤─────────────────────────────────────│
│ 2022-01-02 02:00:11                     │ 2022-01-02 02:00:11                              │ NULL                                │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

SQL Syntax

-- Convert a string or integer to a timestamp
TRY_TO_TIMESTAMP(<expr>)

-- Convert a string to a timestamp using the given pattern
TRY_TO_TIMESTAMP(<expr, expr>)

Aliases

TRY_TO_DATETIME

SQL Examples

SELECT TRY_TO_TIMESTAMP('2022-01-02 02:00:11'), TRY_TO_DATETIME('2022-01-02 02:00:11');

┌──────────────────────────────────────────────────────────────────────────────────┐
│ try_to_timestamp('2022-01-02 02:00:11') │ try_to_datetime('2022-01-02 02:00:11') │
│                Timestamp                │                Timestamp               │
├─────────────────────────────────────────┼────────────────────────────────────────┤
│ 2022-01-02 02:00:11                     │ 2022-01-02 02:00:11                    │
└──────────────────────────────────────────────────────────────────────────────────┘

SELECT TRY_TO_TIMESTAMP('databend'), TRY_TO_DATETIME('databend');

┌────────────────────────────────────────────────────────────┐
│ try_to_timestamp('databend') │ try_to_datetime('databend') │
├──────────────────────────────┼─────────────────────────────┤
│ NULL                         │ NULL                        │
└────────────────────────────────────────────────────────────┘

55 - WEEK

Alias for TO_WEEK_OF_YEAR.

56 - WEEKOFYEAR

Alias for TO_WEEK_OF_YEAR.

57 - YEAR

Alias for TO_YEAR.

58 - YESTERDAY

Returns yesterday date, same as today() - 1.

Analyze Syntax

func.yesterday()

Analyze Examples

func.yesterday()
+------------------+
| func.yesterday() |
+------------------+
| 2021-09-02       |
+------------------+

SQL Syntax

YESTERDAY()

Return Type

DATE, returns date in “YYYY-MM-DD” format.

SQL Examples

SELECT YESTERDAY();
+-------------+
| YESTERDAY() |
+-------------+
| 2021-09-02  |
+-------------+

SELECT TODAY()-1;
+---------------+
| (TODAY() - 1) |
+---------------+
| 2021-09-02    |
+---------------+