Article date

Date Transformations with the Functions Node

The most important ⚠️ first step when dealing with date and time data is to understand the data type you're dealing with, in order to find the correct function.

This article will run through the different date and time data types and the most common functions you can use to work with them in Y42. All functions are available in the Functions Node.

The functions are grouped by:

Different types of date and time data

Data Type Details Y42 Icon

DATE
  • Definition: Calendar date
  • Example: 2022-01-06
 Group_6528.png

DATETIME
  • Definition: Calendar date and time
  • Example: 2022-01-06 08:40:26
 Group_6734.png

TIMESTAMP
  • Definition: Calendar date and time which converts from the current time to UTC and vice-versa and differs with current time zone settings
  • Example: 2022-01-06T08:40:26+00:00
 Group_6734.png

 

Functions to manipulate the format of date and time data

Function Details

CONVERT_TO_DATE

  • Definition: Implicitly cast date/timestamp/datetime to date
  • Parameters data types: DATE, DATETIME or TIMESTAMP
  • Output data type: DATE  
 
Function:
CONVERT_TO_DATE(
'2020-10-18T13:45:55')

Result:
2020-10-18

CONVERT_TO_DATETIME

  • Definition: Implicitly cast date/timestamp/datetime to datetime
  • Parameters data types: DATE, DATETIME or TIMESTAMP
  • Output data type: DATETIME 
Function:
CONVERT_TO_DATETIME(
'2020-10-18T13:45:55')

Result: 
2020-10-18 13:45:55

CONVERT_TO_TIMESTAMP

  • Definition: Implicitly cast date/timestamp/datetime to timestamp
  • Parameters data types: DATE, DATETIME or TIMESTAMP
  • Output data type: TIMESTAMP 
Function:
CONVERT_TO_TIMESTAMP(
'2020-10-18 13:45:55')

Result:
2020-10-18T13:45:55

FORMAT_DATE

  • Definition: Formats a date column according to the specified format
  • Parameters data types: DATE
  • Output data type: TEXT 
Function:
FORMAT_DATE(
'2008-12-25',
'%b-%d-%Y')

Result: 
'Dec-25-2008'

FORMAT_DATETIME

  • Definition: Formats a datetime column according to the specified format
  • Parameters data types: DATETIME
  • Output data type: TEXT 
 Function:
FORMAT_DATETIME(
'2008-12-25 15:30:00', 
'%b-%d-%Y') 

Result:
'Dec-25-2008'

FORMAT_TIMESTAMP

  • Definition: Formats a timestamp column according to the specified format
  • Parameters data types: TIMESTAMP
  • Output data type: TEXT 
Function:
FORMAT_TIMESTAMP(
'2008-12-25T15:30:00', 
'%b-%d-%Y')

Result:
'Dec-25-2008'

LAST_DAY

  • Definition: Returns the last day of the month from a datetime
  • Parameters data types: DATE, DATETIME, or TIMESTAMP
  • Output data type: DATE 
Function:
LAST_DAY(
'2020-10-18')  

Result:
2020-10-31

DATE_TRUNC

  • Definition: Truncates the date to the specified granularity
  • Parameters data types: 
    • Date (DATE)
    • Interval (TEXT*)
  • Output data type: DATE

*Allowed values: DAY, WEEK, ISOWEEK, MONTH, QUARTER, YEAR, ISOYEAR 

Function:
DATE_TRUNC(
'2020-10-18','MONTH')

Result:
2020-10-01

EXTRACT

  • Definition: Returns a value from datetime that corresponds to the specified part
  • Parameters data types: 
    • Date (DATETIME)
    • Interval (TEXT*)
  • Output data type: NUMBER

*Allowed values: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAYOFWEEK, DAY, DAYOFYEAR, WEEK, ISOWEEK, MONTH, QUARTER, YEAR, ISOYEAR 

  Function:
EXTRACT(
'2020-10-18 13:45:00',
'DAY')

Result: 
18

 

Functions to perform calculations with date and time data

Function Details

DATE_ADD

  • Definition: Adds a valuePart to a date and then returns the date
  • Parameters data types: 
    • Date: DATE
    • Interval: TEXT*
    • Value: NUMBER
  • Output data type: DATE 

*Allowed values: DAY, WEEK, MONTH, QUARTER, YEAR 

 Function:
DATE_ADD(
'2013-05-07','DAY',2)

Result:
2013-05-09

DATETIME_ADD

  • Definition: Adds a valuePart to a date and then returns the datetime
  • Parameters data types: 
    • Date: DATETIME
    • Interval: TEXT*
    • Value: NUMBER
  • Output data type: DATETIME

*Allowed values: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR 

Function:
DATETIME_ADD(
'2020-05-07 10:30:00',
'HOUR',4)

Result: 
2020-05-07 14:30:00

TIMESTAMP_ADD

  • Definition: Adds a valuePart to a date and then returns the timestamp
  • Parameters data types: 
    • Date: TIMESTAMP
    • Interval: TEXT*
    • Value: NUMBER
  • Output data type: TIMESTAMP

* Allowed values: MILLISECOND, MICROSECOND, SECOND, MINUTE, HOUR, DAY 

Function:
TIMESTAMP_ADD(
'2020-10-18T13:00:00',
'HOUR',2)  

Result:
2020-10-18T15:00:00

DATE_SUB

  • Definition: Subtract a valuePart to a date and then returns the date
  • Parameters data types: 
    • Date: DATE
    • Interval: TEXT*
    • Value: NUMBER
  • Output data type: DATE

*Allowed values: DAY, WEEK, MONTH, QUARTER, YEAR 

Function:
DATE_SUB(
'2013-05-07','DAY',1)  

Result:
2013-05-06

DATETIME_SUB

  • Definition: Subtract a valuePart to a datetime and then returns the datetime
  • Parameters data types: 
    • Date: DATETIME
    • Interval: TEXT*
    • Value: NUMBER
  • Output data type: DATETIME

*Allowed values: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR 

Function:
DATETIME_SUB(
'2020-05-07 10:30:00',
'HOUR',2)

Result:
2020-05-07 08:30:00

TIMESTAMP_SUB

  • Definition: Subtract a valuePart to a timestamp and then returns the timestamp
  • Parameters data types: 
    • Date: TIMESTAMP
    • Interval: TEXT*
    • Value: NUMBER
  • Output data type: TIMESTAMP

* Allowed values: MILLISECOND, MICROSECOND, SECOND, MINUTE, HOUR, DAY 

Function:
TIMESTAMP_SUB(
'2020-10-18T13:00:00',
'HOUR',2)

Result: 
2020-10-18T11:00:00

DATE_DIFF

  • Definition: Returns the number of whole specified part intervals between two date objects
  • Parameters data types: 
    • Date1: DATE
    • Date2:DATE
    • Interval: TEXT*
  • Output data type: NUMBER

*Allowed values: DAY, WEEK, MONTH, QUARTER, YEAR 

Function:
DATE_DIFF(
'2020-05-07',
'2020-05-04','DAY')

Result:
3

DATETIME_DIFF

  • Definition: Returns the number of whole specified part intervals between two datetime objects
  • Parameters data types: 
    • Date1: DATETIME
    • Date2:DATETIME
    • Interval: TEXT*
  • Output data type: NUMBER

*Allowed values: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR 

Function:
DATETIME_DIFF(
'2020-05-07 10:30:00',
'2020-05-04 08:30:00,
''HOUR')
  
Result:
2

TIMESTAMP_DIFF

  • Definition: Returns the number of whole specified part intervals between two timestamp objects
  • Parameters data types: 
    • Date1: DATETIME
    • Date2:DATETIME
    • Interval: TEXT*
  • Output data type: NUMBER

* Allowed values: MILLISECOND, MICROSECOND, SECOND, MINUTE, HOUR, DAY 

Function:
TIMESTAMP_DIFF(
'2020-05-07T10:30:00',
'2020-05-04T08:30:00','
HOUR')

Result:
2

 

Functions to turn text data into date and time data

Function Details

CAST_DATETIME

  • Definition: Converts a text column to type datetime
  • Parameters data types: TEXT, TIMESTAMP, DATE, or DATETIME
  • Output data type: DATETIME 
Function:
CAST_DATETIME(
'2013-05-07T13:00:00')

Result:
2013-05-07 13:00:00

CAST_TIMESTAMP

  • Definition: Converts a text column to type timestamp
  • Parameters data types: TEXT, TIMESTAMP, DATE, or DATETIME
  • Output data type: TIMESTAMP 
Function:
CAST_TIMESTAMP(
'2013-05-07 13:00:00')

Result:
2013-05-07T13:00:00

PARSE_DATE

  • Definition: Converts a text column to type date using a self-defined format
  • Parameters data types: 
    • Date: TEXT
    • Format: TEXT
  • Output data type: DATE

For more info on format visit this page 

 Function:
PARSE_DATE(
'2020-10-18',
'%Y-%m-%d')

Result:
2020-10-18

PARSE_DATETIME

  • Definition: Converts a text column to type datetime using a self-defined format
  • Parameters data types: 
    • Date: TEXT
    • Format: TEXT
  • Output data type: DATETIME

For more info on format visit this page 

Function
PARSE_DATETIME(
'22013-05-07 13:30:00',
'%Y-%m-%d %H:%M:%S')

Result:
2013-05-07 13:30:00

PARSE_TIMESTAMP

  • Definition: Converts a text column to type timestamp using a self-defined format
  • Parameters data types: 
    • Date: TEXT
    • Format: TEXT
  • Output data type: TIMESTAMP

For more info on format visit this page 

Function:
PARSE_TIMESTAMP(
'22013-05-07 13:30:00',
'%Y-%m-%d %H:%M:%S')
  
Result: 
2013-05-07 13:30:00

Articles in this section

See more
Was this article helpful?
0 out of 0 found this helpful