New variable methods

Category: Common

Case (if/else)

Use case to utilize if-then-else logic, assigning values when expressions evaluate to true –> learn more
CASE
@case
ELSE @defaultValue
END

Return type

dynamic (input-dependent)

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@case
cases
true
-
@defaultValue
variable or literal
true
(Value)

Category: Other

Cast

Converts the type of a variable. –> learn more
SAFE_CAST(@expression AS @castType)

Return type

dynamic (input-dependent)

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@expression
variable or literal
true
(Variable)
@castType
enum
any of: INT64, FLOAT64, STRING, TIME, DATETIME, DATE, BOOLEAN
true
(New type)
@safeCast
boolean
any boolean
true
-

Constant

Create a constant value as a variable –> learn more
@literal

Return type

dynamic (input-dependent)

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@literal
literal
true
-

Hash

Returns an MD5 hash of all values as a base64 encoded string. Non-string values will be first coerced to strings. Note that order of inputs will affect the hash. –> learn more
TO_BASE64(MD5(CONCAT(@expression)))

Return type

string

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@expression
true
(values)

UUID

Generate a random universally unique identifier (UUID) –> learn more
GENERATE_UUID()

Return type

string

Parameters

Category: Date

Current date

Returns the current date –> learn more
CURRENT_DATE([@time_zone])

Return type

date

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@time_zone
enum
any of: ACDT, ACST, ACT, ACT, ACWST, ADT, AEDT, AEST, AFT, AKDT, AKST, AMST, AMT, AMT, ART, AST, AST, AWST, AZOST, AZOT, AZT, BDT, BIOT, BIT, BOT, BRST, BRT, BST, BST, BST, BTT, CAT, CCT, CDT, CDT, CEST, CET, CHADT, CHAST, CHOT, CHOST, CHST, CHUT, CIST, CIT, CKT, CLST, CLT, COST, COT, CST, CST, CST, CT, CVT, CWST, CXT, DAVT, DDUT, DFT, EASST, EAST, EAT, ECT, ECT, EDT, EEST, EET, EGST, EGT, EIT, EST, FET, FJT, FKST, FKT, FNT, GALT, GAMT, GET, GFT, GILT, GIT, GMT, GST, GST, GYT, HDT, HAEC, HST, HKT, HMT, HOVST, HOVT, ICT, IDLW, IDT, IOT, IRDT, IRKT, IRST, IST, IST, IST, JST, KALT, KGT, KOST, KRAT, KST, LHST, LHST, LINT, MAGT, MART, MAWT, MDT, MET, MEST, MHT, MIST, MIT, MMT, MSK, MST, MST, MUT, MVT, MYT, NCT, NDT, NFT, NPT, NST, NT, NUT, NZDT, NZST, OMST, ORAT, PDT, PET, PETT, PGT, PHOT, PHT, PKT, PMDT, PMST, PONT, PST, PST, PYST, PYT, RET, ROTT, SAKT, SAMT, SAST, SBT, SCT, SDT, SGT, SLST, SRET, SRT, SST, SST, SYOT, TAHT, THA, TFT, TJT, TKT, TLT, TMT, TRT, TOT, TVT, ULAST, ULAT, UTC, UYST, UYT, UZT, VET, VLAT, VOLT, VOST, VUT, WAKT, WAST, WAT, WEST, WET, WIT, WST, YAKT, YEKT
false
(UTC (default))

New date

Constructs a date from a year, month, and day –> learn more
DATE(@year, @month, @day)

Return type

date

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@day
variable or literal
any integer
true
(day)
@month
variable or literal
any integer
true
(month)
@year
variable or literal
any integer
true
(year)

Date add

Add a period of time to a date –> learn more
DATE_ADD(@date_expression, INTERVAL @integer_expression @date_part)

Return type

date

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@date_expression
variable or literal
any date
true
(date)
@integer_expression
variable or literal
any integer
true
(number)
@date_part
enum
any of: day, week, month, quarter, year
true
(Date part (e.g., days))

Date diff

Calculate distance between two dates –> learn more
DATE_DIFF(@date_expression, @date_expression2, @date_part)

Return type

integer

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@date_expression
variable or literal
any date
true
(date)
@date_expression2
variable or literal
any date
true
(date)
@date_part
enum
any of: day, week, month, quarter, year
true
(Date part (e.g., days))

Date subtract

Subtract a period of time from a date –> learn more
DATE_SUB(@date_expression, INTERVAL @integer_expression @date_part)

Return type

date

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@date_expression
variable or literal
any date
true
(date)
@integer_expression
variable or literal
any integer
true
(number)
@date_part
enum
any of: day, week, month, quarter, year
true
(Date part (e.g., days))

Date truncate

Truncates a date to the nearest boundary –> learn more
DATE_TRUNC(@date_expression, @date_truncate_part)

Return type

date

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@date_expression
variable or literal
any date
true
(date)
@date_truncate_part
enum
any of: day, month, quarter, year, week(sunday), week(monday), week(tuesday), week(wednesday), week(thursday), week(friday), week(saturday), ISOquarter, ISOyear
true
(Date part (e.g., month))

Date extract

Extracts the date part (e.g, month) from a date –> learn more
EXTRACT(@date_part FROM @date_expression)

Return type

integer

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@date_expression
variable or literal
any date, dateTime, time
true
(value)
@date_part
enum
any of: DAYOFWEEK, DAY, DAYOFYEAR, WEEK, MONTH, QUARTER, YEAR, ISOWEEK, ISOYEAR
true
(Date part (e.g., month))

Format date

Returns a formatted string from a date –> learn more
FORMAT_DATE(@format_string, @date_expression)

Return type

string

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@format_string
literal
any string
true
(format string)
@date_expression
variable or literal
any date
true
(date)
More details about format strings here

Parse date

Parses a date from a string –> learn more
[@SAFE.]PARSE_DATE(@format_string, @date_string)

Return type

date

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@format_string
literal
any string
true
(format string)
@date_string
variable or literal
any string
true
(date string)
@SAFE
boolean
any boolean
false
-
More details about format strings here

Category: DateTime

Current dateTime

Returns the current dateTime –> learn more
CURRENT_DATETIME([@time_zone])

Return type

dateTime

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@time_zone
enum
any of: ACDT, ACST, ACT, ACT, ACWST, ADT, AEDT, AEST, AFT, AKDT, AKST, AMST, AMT, AMT, ART, AST, AST, AWST, AZOST, AZOT, AZT, BDT, BIOT, BIT, BOT, BRST, BRT, BST, BST, BST, BTT, CAT, CCT, CDT, CDT, CEST, CET, CHADT, CHAST, CHOT, CHOST, CHST, CHUT, CIST, CIT, CKT, CLST, CLT, COST, COT, CST, CST, CST, CT, CVT, CWST, CXT, DAVT, DDUT, DFT, EASST, EAST, EAT, ECT, ECT, EDT, EEST, EET, EGST, EGT, EIT, EST, FET, FJT, FKST, FKT, FNT, GALT, GAMT, GET, GFT, GILT, GIT, GMT, GST, GST, GYT, HDT, HAEC, HST, HKT, HMT, HOVST, HOVT, ICT, IDLW, IDT, IOT, IRDT, IRKT, IRST, IST, IST, IST, JST, KALT, KGT, KOST, KRAT, KST, LHST, LHST, LINT, MAGT, MART, MAWT, MDT, MET, MEST, MHT, MIST, MIT, MMT, MSK, MST, MST, MUT, MVT, MYT, NCT, NDT, NFT, NPT, NST, NT, NUT, NZDT, NZST, OMST, ORAT, PDT, PET, PETT, PGT, PHOT, PHT, PKT, PMDT, PMST, PONT, PST, PST, PYST, PYT, RET, ROTT, SAKT, SAMT, SAST, SBT, SCT, SDT, SGT, SLST, SRET, SRT, SST, SST, SYOT, TAHT, THA, TFT, TJT, TKT, TLT, TMT, TRT, TOT, TVT, ULAST, ULAT, UTC, UYST, UYT, UZT, VET, VLAT, VOLT, VOST, VUT, WAKT, WAST, WAT, WEST, WET, WIT, WST, YAKT, YEKT
false
(UTC (default))

New dateTime

Constructs a dateTime from a year, month, day, hour, minute, and second –> learn more
DATETIME(@year, @month, @day, @hour, @minute, @second)

Return type

dateTime

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@day
variable or literal
any integer
true
(integer)
@month
variable or literal
any integer
true
(integer)
@year
variable or literal
any integer
true
(integer)
@hour
variable or literal
any integer
true
(integer)
@minute
variable or literal
any integer
true
(integer)
@second
variable or literal
any integer
true
(integer)

DateTime add

Add a period of time to a dateTime –> learn more
DATETIME_ADD(@dateTime_expression, INTERVAL @integer_expression @date_part)

Return type

dateTime

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@dateTime_expression
variable or literal
any dateTime
true
(dateTime)
@integer_expression
variable or literal
any integer
true
(number)
@date_part
enum
any of: microsecond, millisecond, second, minute, hour, day, week, month, quarter, year
true
(Date part (e.g., days))

DateTime diff

Calculate distance between two dateTimes –> learn more
DATETIME_DIFF(@dateTime_expression, @dateTime_expression2, @date_part)

Return type

integer

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@dateTime_expression
variable or literal
any dateTime
true
(end dateTime)
@dateTime_expression2
variable or literal
any dateTime
true
(start dateTime)
@date_part
enum
any of: microsecond, millisecond, second, minute, hour, day, week, month, quarter, year
true
(Date part (e.g., days))

DateTime subtract

Subtract a period of time from a dateTime –> learn more
DATETIME_SUB(@dateTime_expression, INTERVAL @integer_expression @date_part)

Return type

dateTime

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@dateTime_expression
variable or literal
any dateTime
true
(dateTime)
@integer_expression
variable or literal
any integer
true
(number)
@date_part
enum
any of: microsecond, millisecond, second, minute, hour, day, week, month, quarter, year
true
(Date part (e.g., days))

DateTime truncate

Truncates a dateTime to the nearest boundary –> learn more
DATETIME_TRUNC(@dateTime_expression, @date_truncate_part)

Return type

dateTime

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@dateTime_expression
variable or literal
any dateTime
true
(dateTime)
@date_truncate_part
enum
any of: microsecond, millisecond, second, minute, hour, day, month, quarter, year, week(sunday), week(monday), week(tuesday), week(wednesday), week(thursday), week(friday), week(saturday), ISOquarter, ISOyear
true
(DateTime part (e.g., minutes))

DateTime extract

Extracts the dateTime part (e.g, hour) from a dateTime –> learn more
EXTRACT(@date_part FROM @date_expression)

Return type

integer

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@date_expression
variable or literal
any dateTime
true
(value)
@date_part
enum
any of: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAYOFWEEK, DAYOFWEEK, DAYOFWEEK, DAY, DAYOFYEAR, WEEK, MONTH, QUARTER, YEAR, ISOWEEK, ISOYEAR
true
(DateTime part (e.g., hour))

Format dateTime

Returns a formatted string from a dateTime –> learn more
FORMAT_DATETIME(@format_string, @dateTime_expression)

Return type

string

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@format_string
literal
any string
true
(format string)
@dateTime_expression
variable or literal
any dateTime
true
(dateTime)
More details about format strings here

Parse dateTime

Parses a dateTime from a string –> learn more
[@SAFE.]PARSE_DATETIME(@format_string, @dateTime_string)

Return type

dateTime

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@format_string
literal
any string
true
(format string)
@dateTime_string
variable or literal
any string
true
(dateTime string)
@SAFE
boolean
any boolean
false
-
More details about format strings here

Category: Time

Current time

Returns the current time –> learn more
CURRENT_TIME()

Return type

time

Parameters

Format time

Returns a formatted string from a time –> learn more
FORMAT_TIME(@format_string, @time_expression)

Return type

string

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@format_string
literal
any string
true
(format string)
@time_expression
variable or literal
any time
true
(time)
More details about format strings here

Parse time

Parses a time from a string –> learn more
[@SAFE.]PARSE_TIME(@format_string, @time_string)

Return type

time

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@format_string
literal
any string
true
(format string)
@time_string
variable or literal
any string
true
(time string)
@SAFE
boolean
any boolean
false
-
More details about format strings here

New time

Constructs a time from an hour, minute, and second –> learn more
TIME(@hour, @minute, @second)

Return type

time

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@hour
variable or literal
any integer
true
(integer)
@minute
variable or literal
any integer
true
(integer)
@second
variable or literal
any integer
true
(integer)

Time add

Add a period of time to a time –> learn more
TIME_ADD(@time_expression, INTERVAL @integer_expression @date_part)

Return type

time

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@time_expression
variable or literal
any time
true
(time)
@integer_expression
variable or literal
any integer
true
(number)
@date_part
enum
any of: microsecond, millisecond, second, minute, hour
true
(Time part (e.g., minutes))

Time diff

Calculate distance between two times –> learn more
TIME_DIFF(@time_expression, @time_expression2, @date_part)

Return type

integer

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@time_expression
variable or literal
any time
true
(end time)
@time_expression2
variable or literal
any time
true
(start time)
@date_part
enum
any of: microsecond, millisecond, second, minute, hour
true
(Time part (e.g., minutes))

Time subtract

Subtract a period of time from a time –> learn more
TIME_SUB(@time_expression, INTERVAL @integer_expression @date_part)

Return type

time

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@time_expression
variable or literal
any time
true
(time)
@integer_expression
variable or literal
any integer
true
(number)
@date_part
enum
any of: microsecond, millisecond, second, minute, hour
true
(Time part (e.g., minutes))

Time truncate

Truncates a time to the nearest boundary –> learn more
TIME_TRUNC(@time_expression, @date_truncate_part)

Return type

time

Parameters

Name
Type
Allowed values
Required
Placeholder (in UI)
@time_expression