FX - Date @ Data transformation (ETL) @ MentDB Weak
Date
date adddate addtdate current_msdate current_nsdate curdatedate current_datedate current_timedate current_timestampdate curtimedate curtimestampdate dateadddate dateaddtdate datediffdate datedifftdate ts_to_longdate dt_to_longdate long_to_dtdate long_to_tsdate day_of_monthdate day_of_weekdate day_of_yeardate daynamedate diffdate difftdate full_systimestampdate hourdate is_valid_datedate is_valid_datedate is_valid_timedate is_valid_timestampdate is_valid_timestampdate formatdate minutedate monthdate monthnamedate nb_daydate secondedate sysdatedate systimedate systimestampdate nowdate systimestamp_mindate timedate yeardate epochdate timezonedate systimestamp_utcdate first_day_of_monthdate last_day_of_monthdate first_day_of_yeardate last_day_of_yeardate first_day_of_weekdate last_day_of_weekdate week_of_yeardate quarterdate is_leap_yeardate days_in_monthdate is_weekenddate agedate to_isodate from_isodate to_epochdate from_epochdate to_utcdate from_utcdate next_daydate add_business_daysdate nb_business_daysdate is_beforedate is_afterdate truncdate durationdate betweendate convert_tz
date add <date> <field> <number>
Description
Add a number to a date.
Add a number to a date.
Parameters
date: The date - String - required
field: The field to add (DAY|MONTH|YEAR) - String - required
number: The number to add - Number - required
date: The date - String - required
field: The field to add (DAY|MONTH|YEAR) - String - required
number: The number to add - Number - required
admin
date add "1980-06-18" "DAY" 1
mentdb
1980-06-19
date addt <timestamp> <field> <number>
Description
Add a number to a timestamp.
Add a number to a timestamp.
Parameters
timestamp: The timestamp - String - required
field: The field to add (SEC|MIN|HOUR|DAY|MONTH|YEAR) - String - required
number: The number to add - Number - required
timestamp: The timestamp - String - required
field: The field to add (SEC|MIN|HOUR|DAY|MONTH|YEAR) - String - required
number: The number to add - Number - required
admin
date addt "1980-06-18 00:00:00" "DAY" 1
mentdb
1980-06-19 00:00:00
date current_ms
Description
Returns the current time in milliseconds.
Returns the current time in milliseconds.
admin
date current_ms
mentdb
1496164604097
date current_ns
Description
Returns the current time in nanoseconds.
Returns the current time in nanoseconds.
admin
date current_ns
mentdb
823603146305304
date curdate
Description
Returns the current date.
Returns the current date.
admin
date curdate
mentdb
2015-09-13
date current_date
Description
Returns the current date.
Returns the current date.
admin
date current_date
mentdb
2015-09-13
date current_time
Description
Returns the current time.
Returns the current time.
admin
date current_time
mentdb
14:01:22
date current_timestamp
Description
Returns the current timestamp.
Returns the current timestamp.
admin
date current_timestamp
mentdb
2015-09-13 14:01:42
date curtime
Description
Returns the current time.
Returns the current time.
admin
date curtime
mentdb
14:01:57
date curtimestamp
Description
Returns the current timestamp.
Returns the current timestamp.
admin
date curtimestamp
mentdb
2015-09-13 14:02:13
date dateadd <date> <field> <number>
Description
Add a number to a date.
Add a number to a date.
Parameters
date: The date - String - required
field: The field to add (DAY|MONTH|YEAR) - String - required
number: The number to add - Number - required
date: The date - String - required
field: The field to add (DAY|MONTH|YEAR) - String - required
number: The number to add - Number - required
admin
date dateadd "1980-06-18" "DAY" 1
mentdb
1980-06-19
date dateaddt <timestamp> <field> <number>
Description
Add a number to a timestamp.
Add a number to a timestamp.
Parameters
timestamp: The timestamp - String - required
field: The field to add (SEC|MIN|HOUR|DAY|MONTH|YEAR) - String - required
number: The number to add - Number - required
timestamp: The timestamp - String - required
field: The field to add (SEC|MIN|HOUR|DAY|MONTH|YEAR) - String - required
number: The number to add - Number - required
admin
date dateaddt "1980-06-18 00:00:00" "DAY" 1
mentdb
1980-06-19 00:00:00
date datediff <date> <field> <number>
Description
Subtract a number from a date.
Subtract a number from a date.
Parameters
date: The date - String - required
field: The field to add (DAY|MONTH|YEAR) - String - required
number: The number to add - Number - required
date: The date - String - required
field: The field to add (DAY|MONTH|YEAR) - String - required
number: The number to add - Number - required
admin
date datediff "1980-06-18" "DAY" 1
mentdb
1980-06-17
date datedifft <timestamp> <field> <number>
Description
Subtract a number from a date.
Subtract a number from a date.
Parameters
timestamp: The timestamp - String - required
field: The field to add (SEC|MIN|HOUR|DAY|MONTH|YEAR) - String - required
number: The number to add - Number - required
timestamp: The timestamp - String - required
field: The field to add (SEC|MIN|HOUR|DAY|MONTH|YEAR) - String - required
number: The number to add - Number - required
admin
date datedifft "1980-06-18 00:00:00" "DAY" 1
mentdb
1980-06-17 00:00:00
date ts_to_long <timestamp>
Description
Converts a timestamp to a long int.
Converts a timestamp to a long int.
Parameters
timestamp: The timestamp - String - required
timestamp: The timestamp - String - required
admin
date ts_to_long "1980-06-18 00:00:00"
mentdb
330120000000
date dt_to_long <date>
Description
Converts the date parameter to a long int.
Converts the date parameter to a long int.
Parameters
date: The date - String - required
date: The date - String - required
admin
date dt_to_long "1980-06-18"
mentdb
330120000000
date long_to_dt <date>
Description
Converts the date parameter sent by the user to a long int.
Converts the date parameter sent by the user to a long int.
Parameters
date: The date - String - required
date: The date - String - required
admin
date long_to_dt "330120000000"
mentdb
1980-06-18
date long_to_ts <date>
Description
Converts a long int to a timestamp.
Converts a long int to a timestamp.
Parameters
date: The date - String - required
date: The date - String - required
admin
date long_to_ts "330120000000"
mentdb
1980-06-18 00:00:00
date day_of_month <date>
Description
Returns the day specified in the date sent in parameter.
Returns the day specified in the date sent in parameter.
Parameters
date: The date - String - required
date: The date - String - required
admin
date day_of_month "1980-06-18"
mentdb
18
date day_of_week <date>
Description
Returns the week value of the date sent as parameter.
Returns the week value of the date sent as parameter.
Parameters
date: The date - String - required
date: The date - String - required
admin
date day_of_week "1980-06-18"
mentdb
3
date day_of_year <date>
Description
Returns the year value of the date sent as parameter.
Returns the year value of the date sent as parameter.
Parameters
date: The date - String - required
date: The date - String - required
admin
date day_of_year "1980-06-18"
mentdb
170
date dayname <date>
Description
Returns the day name of the date sent as parameter.
Returns the day name of the date sent as parameter.
Parameters
date: The date - String - required
date: The date - String - required
admin
date dayname "1980-06-18"
mentdb
wednesday
date diff <date> <field> <number>
Description
Subtract a number from a date.
Subtract a number from a date.
Parameters
date: The date - String - required
field: The field to add (DAY|MONTH|YEAR) - String - required
number: The number to add - Number - required
date: The date - String - required
field: The field to add (DAY|MONTH|YEAR) - String - required
number: The number to add - Number - required
admin
date diff "1980-06-18" "DAY" 1
mentdb
1980-06-17
date difft <timestamp> <field> <number>
Description
Subtract a number from a timestamp.
Subtract a number from a timestamp.
Parameters
timestamp: The timestamp - String - required
field: The field to add (SEC|MIN|HOUR|DAY|MONTH|YEAR) - String - required
number: The number to add - Number - required
timestamp: The timestamp - String - required
field: The field to add (SEC|MIN|HOUR|DAY|MONTH|YEAR) - String - required
number: The number to add - Number - required
admin
date difft "1980-06-18 00:00:00" "DAY" 1
mentdb
1980-06-17 00:00:00
date full_systimestamp
Description
Returns the current full timestamp.
Returns the current full timestamp.
admin
date full_systimestamp
mentdb
2015-09-13 14:03:20.395+0400
date hour <time>
Description
Returns the hour value of the time sent as parameter.
Returns the hour value of the time sent as parameter.
Parameters
time: The time - String - required
time: The time - String - required
admin
date hour "12:15:56"
mentdb
12
date is_valid_date <dateToValidate>
Description
Check if a date is valid.
Check if a date is valid.
Parameters
dateToValidate: The date - String - required
dateToValidate: The date - String - required
admin
date is_valid_date "1980-06-18"
mentdb
1
date is_valid_date <dateToValidate> <format>
Description
Check if a date is valid.
Check if a date is valid.
Parameters
dateToValidate: The date - String - required
format: The date format - String - required
dateToValidate: The date - String - required
format: The date format - String - required
admin
date is_valid_date "1980-06-18" "yyyy-MM-dd"
mentdb
1
date is_valid_time <timeToValidate>
Description
Check if a time is valid.
Check if a time is valid.
Parameters
timeToValidate: The time (12:00:01) - String - required
timeToValidate: The time (12:00:01) - String - required
admin
date is_valid_time "12:00:01"
mentdb
1
date is_valid_timestamp <timestampToValidate>
Description
Check if a timestamp is valid.
Check if a timestamp is valid.
Parameters
timestampToValidate: The timestamp - String - required
timestampToValidate: The timestamp - String - required
admin
date is_valid_timestamp "1980-06-18 12:00:01"
mentdb
1
date is_valid_timestamp <timestampToValidate> <format>
Description
Check if a timestamp is valid.
Check if a timestamp is valid.
Parameters
timestampToValidate: The timestamp - String - required
format: The format (ex: yyyy MM dd HH:mm:ss) - String - required
timestampToValidate: The timestamp - String - required
format: The format (ex: yyyy MM dd HH:mm:ss) - String - required
admin
date is_valid_timestamp "1980-06-18 12:00:01" "yyyy-MM-dd HH:mm:ss"
mentdb
1
date format <timestampToFormat> <formatIn> <formatOut>
Description
Convert a timestamp to another format.
Convert a timestamp to another format.
Parameters
timestampToFormat: The timestamp - String - required
formatIn: The in format (ex: yyyyMMdd HH:mm) - String - required
formatOut: The out format (ex: yyyy MM dd HH:mm:ss) - String - required
timestampToFormat: The timestamp - String - required
formatIn: The in format (ex: yyyyMMdd HH:mm) - String - required
formatOut: The out format (ex: yyyy MM dd HH:mm:ss) - String - required
admin
date format "19800618 12:00" "yyyyMMdd HH:mm" "yyyy-MM-dd HH:mm:ss"
mentdb
1980-06-18 12:00:01
date minute <time>
Description
Returns the minute value of the time sent as parameter.
Returns the minute value of the time sent as parameter.
Parameters
time: The time - String - required
time: The time - String - required
admin
date minute "12:15:56"
mentdb
15
date month <date>
Description
Returns the month value of a date sent as parameter.
Returns the month value of a date sent as parameter.
Parameters
date: The date - String - required
date: The date - String - required
admin
date month "1980-06-18"
mentdb
6
date monthname <date>
Description
Returns the month name of the date sent as parameter.
Returns the month name of the date sent as parameter.
Parameters
date: The date - String - required
date: The date - String - required
admin
date monthname "1980-06-18"
mentdb
juin
date nb_day <date1> <date2>
Description
Get the number of days between two dates
Get the number of days between two dates
Parameters
date1: The date 1 - String - required
date2: The date 2 - String - required
date1: The date 1 - String - required
date2: The date 2 - String - required
admin
date nb_day "1980-06-18" "1980-06-20"
mentdb
2
date seconde <time>
Description
Returns the second value of the time sent as parameter.
Returns the second value of the time sent as parameter.
Parameters
time: The time - String - required
time: The time - String - required
admin
date seconde "12:15:56"
mentdb
56
date sysdate
Description
Returns the current date.
Returns the current date.
admin
date sysdate
mentdb
2015-09-13
date systime
Description
Returns the current time.
Returns the current time.
admin
date systime
mentdb
14:04:18
date systimestamp
Description
Returns the current timestamp.
Returns the current timestamp.
admin
date systimestamp
mentdb
2015-09-13 14:04:32
date now
Description
Returns the current timestamp.
Returns the current timestamp.
admin
date now
mentdb
2015-09-13 14:04:32
date systimestamp_min
Description
Returns the current timestamp in minimal format.
Returns the current timestamp in minimal format.
admin
date systimestamp_min
mentdb
20150913140446
date time <timestamp>
Description
Get the time part of a timestamp.
Get the time part of a timestamp.
Parameters
timestamp: The timestamp - String - required
timestamp: The timestamp - String - required
admin
date time "1980-06-18 12:25:56"
mentdb
12:25:56
date year <date>
Description
Returns the year value of the date sent as parameter.
Returns the year value of the date sent as parameter.
Parameters
date: The date - String - required
date: The date - String - required
admin
date year "1980-06-18"
mentdb
1980
date epoch
Description
Returns the current Unix epoch in seconds.
Returns the current Unix epoch in seconds.
admin
date epoch
mentdb
1761818400
date timezone
Description
Returns the time zone of the server.
Returns the time zone of the server.
admin
date timezone
mentdb
Indian/Reunion
date systimestamp_utc
Description
Returns the current UTC timestamp.
Returns the current UTC timestamp.
admin
date systimestamp_utc
mentdb
2026-10-30 10:00:00
date first_day_of_month <date>
Description
Returns the first day of the month.
Returns the first day of the month.
Parameters
date: The date or timestamp - String - required
date: The date or timestamp - String - required
admin
date first_day_of_month "1980-06-18"
mentdb
1980-06-01
date last_day_of_month <date>
Description
Returns the last day of the month.
Returns the last day of the month.
Parameters
date: The date or timestamp - String - required
date: The date or timestamp - String - required
admin
date last_day_of_month "1980-06-18"
mentdb
1980-06-30
date first_day_of_year <date>
Description
Returns the first day of the year.
Returns the first day of the year.
Parameters
date: The date or timestamp - String - required
date: The date or timestamp - String - required
admin
date first_day_of_year "1980-06-18"
mentdb
1980-01-01
date last_day_of_year <date>
Description
Returns the last day of the year.
Returns the last day of the year.
Parameters
date: The date or timestamp - String - required
date: The date or timestamp - String - required
admin
date last_day_of_year "1980-06-18"
mentdb
1980-12-31
date first_day_of_week <date>
Description
Returns the first day of the ISO week (monday).
Returns the first day of the ISO week (monday).
Parameters
date: The date or timestamp - String - required
date: The date or timestamp - String - required
admin
date first_day_of_week "1980-06-18"
mentdb
1980-06-16
date last_day_of_week <date>
Description
Returns the last day of the ISO week (sunday).
Returns the last day of the ISO week (sunday).
Parameters
date: The date or timestamp - String - required
date: The date or timestamp - String - required
admin
date last_day_of_week "1980-06-18"
mentdb
1980-06-22
date week_of_year <date>
Description
Returns the ISO 8601 week number.
Returns the ISO 8601 week number.
Parameters
date: The date or timestamp - String - required
date: The date or timestamp - String - required
admin
date week_of_year "1980-06-18"
mentdb
25
date quarter <date>
Description
Returns the quarter (1 to 4).
Returns the quarter (1 to 4).
Parameters
date: The date or timestamp - String - required
date: The date or timestamp - String - required
admin
date quarter "1980-06-18"
mentdb
2
date is_leap_year <date>
Description
Check if the year is a leap year.
Check if the year is a leap year.
Parameters
date: The date, timestamp or year - String - required
date: The date, timestamp or year - String - required
admin
date is_leap_year "1980-06-18"
mentdb
1
date days_in_month <date>
Description
Returns the number of days in the month.
Returns the number of days in the month.
Parameters
date: The date or timestamp - String - required
date: The date or timestamp - String - required
admin
date days_in_month "1980-02-18"
mentdb
29
date is_weekend <date>
Description
Check if the date is a saturday or a sunday.
Check if the date is a saturday or a sunday.
Parameters
date: The date or timestamp - String - required
date: The date or timestamp - String - required
admin
date is_weekend "1980-06-21"
mentdb
1
date age <birthdate>
Description
Returns the age in years at the current date.
Returns the age in years at the current date.
Parameters
birthdate: The date or timestamp - String - required
birthdate: The date or timestamp - String - required
admin
date age "1980-06-18"
mentdb
46
date to_iso <timestamp>
Description
Converts a local timestamp to ISO 8601 with the server offset.
Converts a local timestamp to ISO 8601 with the server offset.
Parameters
timestamp: The timestamp or date - String - required
timestamp: The timestamp or date - String - required
admin
date to_iso "1980-06-18 12:00:00"
mentdb
1980-06-18T12:00:00+04:00
date from_iso <iso>
Description
Converts an ISO 8601 date (with or without offset) to a local timestamp.
Converts an ISO 8601 date (with or without offset) to a local timestamp.
Parameters
iso: The ISO 8601 date - String - required
iso: The ISO 8601 date - String - required
admin
date from_iso "1980-06-18T08:00:00Z"
mentdb
1980-06-18 12:00:00
date to_epoch <timestamp>
Description
Converts a local timestamp to a Unix epoch in seconds.
Converts a local timestamp to a Unix epoch in seconds.
Parameters
timestamp: The timestamp or date - String - required
timestamp: The timestamp or date - String - required
admin
date to_epoch "1980-06-18 12:00:00"
mentdb
330163200
date from_epoch <epoch>
Description
Converts a Unix epoch in seconds to a local timestamp.
Converts a Unix epoch in seconds to a local timestamp.
Parameters
epoch: The epoch in seconds - Number - required
epoch: The epoch in seconds - Number - required
admin
date from_epoch "330163200"
mentdb
1980-06-18 12:00:00
date to_utc <timestamp>
Description
Converts a local timestamp to UTC.
Converts a local timestamp to UTC.
Parameters
timestamp: The timestamp or date - String - required
timestamp: The timestamp or date - String - required
admin
date to_utc "1980-06-18 12:00:00"
mentdb
1980-06-18 08:00:00
date from_utc <timestamp>
Description
Converts a UTC timestamp to local time.
Converts a UTC timestamp to local time.
Parameters
timestamp: The timestamp or date - String - required
timestamp: The timestamp or date - String - required
admin
date from_utc "1980-06-18 08:00:00"
mentdb
1980-06-18 12:00:00
date next_day <date> <day>
Description
Returns the next given day strictly after the date.
Returns the next given day strictly after the date.
Parameters
date: The date or timestamp - String - required
day: The day (monday ... sunday, lundi ... dimanche, or 1 ... 7) - String - required
date: The date or timestamp - String - required
day: The day (monday ... sunday, lundi ... dimanche, or 1 ... 7) - String - required
admin
date next_day "1980-06-18" "monday"
mentdb
1980-06-23
date add_business_days <date> <number>
Description
Add (or subtract if negative) business days (monday to friday).
Add (or subtract if negative) business days (monday to friday).
Parameters
date: The date or timestamp - String - required
number: The number of business days - Number - required
date: The date or timestamp - String - required
number: The number of business days - Number - required
admin
date add_business_days "1980-06-20" 1
mentdb
1980-06-23
date nb_business_days <date1> <date2>
Description
Get the number of business days (monday to friday) in [date1, date2[.
Get the number of business days (monday to friday) in [date1, date2[.
Parameters
date1: The date 1 - String - required
date2: The date 2 - String - required
date1: The date 1 - String - required
date2: The date 2 - String - required
admin
date nb_business_days "1980-06-16" "1980-06-23"
mentdb
5
date is_before <timestamp1> <timestamp2>
Description
Check if the first date/timestamp is before the second.
Check if the first date/timestamp is before the second.
Parameters
timestamp1: The timestamp or date 1 - String - required
timestamp2: The timestamp or date 2 - String - required
timestamp1: The timestamp or date 1 - String - required
timestamp2: The timestamp or date 2 - String - required
admin
date is_before "1980-06-18" "1980-06-20 10:00:00"
mentdb
1
date is_after <timestamp1> <timestamp2>
Description
Check if the first date/timestamp is after the second.
Check if the first date/timestamp is after the second.
Parameters
timestamp1: The timestamp or date 1 - String - required
timestamp2: The timestamp or date 2 - String - required
timestamp1: The timestamp or date 1 - String - required
timestamp2: The timestamp or date 2 - String - required
admin
date is_after "1980-06-18" "1980-06-20"
mentdb
0
date trunc <timestamp> <field>
Description
Truncate a timestamp.
Truncate a timestamp.
Parameters
timestamp: The timestamp or date - String - required
field: The field (YEAR|QUARTER|MONTH|WEEK|DAY|HOUR|MIN) - String - required
timestamp: The timestamp or date - String - required
field: The field (YEAR|QUARTER|MONTH|WEEK|DAY|HOUR|MIN) - String - required
admin
date trunc "1980-06-18 12:25:56" "MONTH"
mentdb
1980-06-01 00:00:00
date duration <timestamp1> <timestamp2>
Description
Get a readable duration between two timestamps.
Get a readable duration between two timestamps.
Parameters
timestamp1: The timestamp or date 1 - String - required
timestamp2: The timestamp or date 2 - String - required
timestamp1: The timestamp or date 1 - String - required
timestamp2: The timestamp or date 2 - String - required
admin
date duration "1980-06-18 10:00:00" "1980-06-20 12:05:10"
mentdb
2d 2h 5min 10s
date between <timestamp> <min> <max>
Description
Check if a date/timestamp is between two others (bounds included).
Check if a date/timestamp is between two others (bounds included).
Parameters
timestamp: The timestamp or date - String - required
min: The min - String - required
max: The max - String - required
timestamp: The timestamp or date - String - required
min: The min - String - required
max: The max - String - required
admin
date between "1980-06-18" "1980-06-01" "1980-06-30"
mentdb
1
date convert_tz <timestamp> <from> <to>
Description
Convert a timestamp from a time zone to another (ex: Indian/Reunion, Europe/Paris, UTC, +04:00, LOCAL).
Convert a timestamp from a time zone to another (ex: Indian/Reunion, Europe/Paris, UTC, +04:00, LOCAL).
Parameters
timestamp: The timestamp or date - String - required
from: The source time zone - String - required
to: The target time zone - String - required
timestamp: The timestamp or date - String - required
from: The source time zone - String - required
to: The target time zone - String - required
admin
date convert_tz "1980-06-18 12:00:00" "Indian/Reunion" "Europe/Paris"
mentdb
1980-06-18 10:00:00