Date Functions

Get the Current Date

• Date:now()
• Date:today()

Date:now() returns the current date and time as a datetime value.

datetime dt = Date:now();

Date:today() returns the current date as a date value.

date d = Date:today();

Add an Interval to a Date

• Date:addSeconds(startDate, i)
• Date:addDays(startDate, i)
• Date:addMonths(startDate, i)

Date:addSeconds adds an interval to an existing date or datetime value. The seconds parameter should be a positive or negative integer value that represents the number of seconds that is to be added to the date or datetime value. The function returns a new date or datetime instance and doesn't alter the original value.

date endDate = Date:addSeconds(startDate, 10);

Date:addDays adds an interval to an existing date or datetime value. The seconds parameter should be a positive or negative integer value that represents the number of days that is to be added to the date or datetime value. The function returns a new date or datetime instance and doesn't alter the original value.

date endDate = Date:addDays(startDate, 5);

date endDate = Date:addMonths(startDate, 2);

Get an Interval Between Two Dates

• Date:secondsBetween
• Date:daysBetween
• Date:monthsBetween

Date:secondsBetween returns the total number of seconds between the first and second datetime expressions. The value will be negative if the date given by the second argument is earlier than that of the first argument.

datetime dt1 = Date:fromTimeString("2013-1-20 08:45:12 GMT");
datetime dt2 = Date:fromTimeString("2013-1-20 08:45:40 GMT");
int diff1 = Date:secondsBetween(dt1, dt2);

Date:daysBetween returns the number of full days that have to pass to get from the date returned by the first expression to the date returned by the second expression. The value will be negative if the date given by the second argument is earlier than that of the first argument.

date d1 = Date:fromString("2015-01-01");
date d2 = Date:fromString("2015-01-03");
int days = Date:daysBetween(d1, d2);

Date:monthsBetween returns the number of full months that have to pass to get from the date returned by the first expression to the date returned by the second expression. The value will be negative if the date given by the second argument is earlier than that of the first argument.

date d1 = Date:fromString("2015-01-20);
date d2 = Date:fromString("2015-02-22");
int months = Date:monthsBetween(d1, d2);

Convert from String to Date

• Date:fromString
• Date:fromTimeString

Date:fromString returns a date representation of the string value expression.

date dt = Date:fromString("2013-01-02");

datetime dt = Date:fromTimeString("2013-1-20 08:45:12 GMT");

Convert from bigint to Date

• Date:fromUnixTimestamp

Date:fromUnixTimestamp returns a date representation of the string value expression.

datetime dt = Date:fromUnixTimestamp(1631104351673l);

//or

datetime dt = 1631104351673l; //implicit conversion

Date:fromUnixTimestamp returns a date-time representation of the Bigint value expression. 

Other Date Functions

datetime dt = Date:fromTimeString("2013-1-20 08:45:12 GMT");
int year = Date:extract(dt, "year"); //year = 2013
int month = Date:extract(dt, "month"); //month = 1
int day = Date:extract(dt, "day"); //day = 20
int hour = Date:extract(dt, "hour"); //hour = 8
int minute = Date:extract(dt, "minute"); //minute = 45
int second = Date:extract(dt, "second"); //second = 12

Get the Current Date from the Mez Namespace

The Mez:now() function returns the current time as datetime, and is equivalent to Date:now().

datetime t = Mez:now();

Mez:today() returns today's date in date format, and is equivalent to Date:today(). 

date d = Mez:today();

Localisation of Dates

It's important to note that Helium represents date and datetime values internally and on the database without time zone. Dates and datetime values are only localized on the front-end based on the time zone specified in the end user's Helium profile.

This means that any operand used for built-in functions in the Date namespace are first converted to GMT.

Consider the following example:

• A datetime value representing 2018-05-01 00:00:00 is captured in a DSL web app by a user with time zone GMT+2.
• Internally this date is converted to 2018-04-30 22:00:00.
• Adding 1 month to this results in a value that is maintained internally in the DSL as 2018-05-31 22:00:00. 
• If a user with time zone of GMT+2 views this value on the front-end, it will again be converted based on the user's timezone and thus show 2018-06-01 00:00:00.

It is also important to note that when a datetime is cast to date using SQL or the DSL the time portion will be discarded and internally will be represented as 00:00:00. When localisation is applied to the new value by Helium as it is presented on the frontend, it might appear as a day earlier depending on the currently logged in user's locale.

Additional Mentions and References

• Quick Reference#DateBIFs
• Lesson 16: Math and Date Functions