Date Functions
Former user (Deleted)
Jacques Marais
Former user (Deleted)
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.