Date Functions

Owned by Former user (Deleted)
Last updated: Jul 20, 2023 by Jacques Marais



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:addMonths 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 months 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. If the target month has less days than the given one then the date may be reduced so as to not skip an additional month. A month added to 31st January will result in the 28th of February (non leap year).

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");

Date:fromTimeString returns a date-time representation of the string value expression.  The time zone component of the expression is optional and will default to the logged in user's preferred time zone.

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

Date:extract returns an integer representing the component of the date which is identified by the field name string.

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