Understanding how DateTime fields work in LANSA

Date:20 March 2015
Product/Release:Visual LANSA - All supported versions
Abstract:Understanding how LANSA treats DateTime fields can explain why they can appear to show incorrect values
Submitted By:LANSA Technical Support

The use of DateTime fields in LANSA without understanding how they work can lead to unexpected results at runtime. The usual complaint is that the time portion is showing several hours later/earlier than it should. But once you understand how they work and how to code with them, you will have an application that is open to use from around the world, no matter where your users are, or whether they use daylight savings or not.

DateTime field type explained:

The following document aims to explain DateTime fields and how they are used in LANSA: Getting started with DateTime (PDF 137KB)

Further notes:

As per the document, DateTimes are processed as UTC (Greenwich Mean Time) values in LANSA. This means that DateTimes created programmatically (through literal values, or as output from Intrinsic functions such as AsDateTime) are assumed to be the time at UTC. For example, if I set a date as follows:

#StartDate := := ('20150320123456').AsDateTime(CCYYMMDDHHMMSS)

or

#STD_DTIMX := '2015-03-20 12:34:56'

Each of these statements will produce a date as 12:34:56pm, 20th March 2015. But this is assumed to be at UTC time (i.e. in Greenwich in the UK). When it is 12:34:56pm in the UK, it is actually 23:34:56pm in Sydney, Australia (UTC +11hrs in summer time). So if my #StartDate field has the DUTC attribute disabled (default), when I look at this field on my form/WAM, it will show 11:34pm, despite me hardcoding the time as 12:34pm in my application.

The same situation will also occur if you piece together a DateTime from two separate Date and Time fields.

It all seems too difficult!

If all you do is accept DateTime input from the customer, store those in a database, and later retrieve and display that information, then you do not have to think about timezones and UTC. Whenever a date is input or displayed or stored in the database, the UTC conversion is done automatically by LANSA, so they will see whatever time that they entered.

If you need to hardcode date/times, or create DateTimes from separate dates and times, then the following intrinsic function is all you need to remember: .AsUniversalDateTime()

This intrinsic will subtract the UTC offset from your generated date, to produce the time at UTC for when the local date/time is that value which you are generating. For example, both of these statements will now display 12:34:56pm at runtime:

#StartDate := := ('20150320123456').AsDateTime(CCYYMMDDHHMMSS).AsUniversalDateTime()

or

#StartDate := ('2015-03-20 12:34:56').AsUniversalDateTime()

Alternatively, if your application will never be used outside of your current timezone, you may consider enabling the DUTC flag for DateTime fields as explained in the document. In that case all DateTimes, displayed on screen, or generated in code will be at the same time offset (UTC, or +0).