New Datetime variable coming soon to UniVerse 11.3.2

Datetime is a very important data type for business applications including law enforcement, fire and EMS agencies. For example, when an urgent event happens across different time zones, the vital and high-quality information Computer-Aided Dispatch software requires includes Datetime and time zone support, making it possible to store consistent information about the time of events and transactions.

It’s for these reasons that one of our partners requested some new features to make it more convenient to use Datetime in UniVerse application development on Linux and Solaris.

Internally, datetime objects are stored as a single 64-bit integer representing the number of milliseconds since the Unix epoch: midnight UTC of January 1, 1970. All the datetime before the Unix epoch are represented as negative numbers.

In Universe, we’ve done three things to support the UTC datetime.

  1. New variable @TZ (time zone support).
  2. A set of new BASIC functions in relation to datetime.
  3. New conversion code for Datetime(ICONV/OCONV).

New @TZ for datetime

Since datetime is stored as UTC and users may be at different time zones, we’ve introduced a new @-variable, @TZ.

@TZ is a per-process variable which defaults to an empty string. If @TZ is empty, the timezone in use will refer to the user’s TZ environment variable if it is set, or to the system’s time zone setting if it’s not set. The @TZ value could be set to any time zone supported by the underlying operating system. For Unix and Linux, it could be names supported by the tz database.

When converting datetime from and to the local date and time, @TZ will be used as the local time zone. An exception is that when using ICONV() and OCONV() for datetime, if a timezone is specified in the conversion code or in the data to be converted, the @TZ’s value will be overridden.

New BASIC functions for datetime

Because of the inconvenience of using the existing BASIC functions concerning date and time separately, we’ve introduced several new functions to provide full support to Datetime, making it easier for users to work with datetime in BASIC.

1. NOW function

Use the NOW function to return the numeric value of the internal datetime value. Although the NOW function does not accept any arguments, parentheses are required to identify it as a function.

The internal format for the datetime is a 64-bit integer which represents the milliseconds since the start of Unix epoch: midnight UTC of January 1, 1970. All the datetime before the Unix epoch are represented as negative numbers.

The result of NOW function is independent of the time zone setting.

Syntax

NOW()

Example

PRINT NOW()

PRINT OCONV(NOW(), “DT”)

2. TODATETIME function

Use the TODATETIME to convert the internal values of the local date and time to the internal datetime value.

If a given date or/and time is invalid, STATUS will be set to 1.

Syntax

TODATETIME(date, time)

EXAMPLE

PRINT TODATETIME(DATE(), TIME())

3. TODATE function

Use the TODATE function to convert the internal datetime value to the internal local date value.

If a given datetime is invalid, the function will return empty and STATUS will be set to 1.

Syntax

TODATE(datetime)

EXAMPLE

PRINT TODATE(NOW())

4. TOTIME function

Use the TOTIME function to convert the internal datetime value to the internal local time value.

If a given datetime is invalid, the function will return empty and STATUS will be set to 1.

Syntax

TOTIME(datetime)

Example

PRINT TOTIME(NOW())

5. DATETIMEL function

Return the local date and time with microseconds in a human readable format.

Syntax

DATETIMEL()

Example

PRINT DATETIMEL()

6. DATETIMEZ function

Return the UTC date and time with microseconds in a human readable format.

Syntax

DATETIMEZ()

Example

PRINT DATETIMEZ()

Conversion code for datetime

Here we’ll cover how to extend the formats outputted by OCONV() and ICONV() by using of the conversion code. This code can convert the input datetime from human readable data to datetime (an internal 64-bit integer) for storage. It also can be used to convert the internal datetime back to the human readable data for output.

Currently it supports four categories of formats:

  1. Several commonly used formats;
  2. The traditional conversion for date and time;
  3. The ISO-8601 format;
  4. The RFC-5322 format (Web standard).

1. General conversion code (DT) for ICONV

DT is the general conversion code for all four of the above formats for ICONV(. In ICONV() the general code DT could be used for all the formats. But in OCONV(), an unambiguous datetime conversion code must be specified for a format.

2. Time Zone during the conversion

The time zone used in the conversion is determined in the following priority order:

  1. The time zone specified in the date to be converted
  2. The time zone specified in the conversion code
  3. The @TZ’s value
  4. The TZ environment variable
  5. The system’s time zone setting

Format for commonly used formats

DT[4|D|4D|T|TS|Z][;timezone]

The semicolon (;) is the separator.

The format codes and their corresponding datetime format are shown in the below table:

Format Description
(none) Default format: YYYY-MM-DD HH:MM:SS.mmm
M Date and Time: DD MMM YYYY HH:MM
4 Use 4-digit format for date: MM DD YYYY HH:MM
D Date only: DD MMM YYY
4D Date only with 4-digit format: MM DD YYYY
T Time only: HH:MM
TS Time only with seconds: HH:MM:SS
timezone

Examples

Source line Converted value
DT 2019-08-16 12:59.123
DTM 16 Aug 2019 12:59
DT4 08 16 2019 12:59
DT4D 08 16 2019
DTT 12:59
DTTS 12:59:59

Format for combination of the D and MT conversion codes

DT;[date-code];[time-code][;timezone]

The semicolon (;) is the separator.

Format Description
date-code See the conversion code D for date
time-code See the conversion code MT for time
timezone Timezone name

Examples

Source line Converted value
DT;D/;MTS 08/16/2019 12:59:59
DT;D; 16 AUG 2019
DT;;MTS 12:59:59
DT;D/;MTS;America/New_York 08/16/2019 14:59:59

Format for ISO-8601

DTI[B][R|W][S][Z][2|1|0][;[timezone|offset]]

The semicolon (;) is the separator.

Format Description
(none) Default format: YYYY-MM-DDTHH:MM:SS.sss
B Basic format: YYYYMMDDTHHMMSS.sss
R Ordinal date format: YYYY-DDDTHH:MM:SS.sss
W Week date format: YYYY-Www-DTHH:MM:SS.sss
S Use whitespace as separator: YYYY-MM-DD HH:MM:SS.sss
2 Reserve 2 digits of milliseconds.
1 Reserve 1 digit of milliseconds.
0 Do not display milliseconds.
timezone If time zone is set, should display UTC offset in OCONV result.
offset Format: +08:00, -0700, +09. If set, should display UTC offset in OCONV result.
Z Convert to a UTC time. DO NOT set Z and timezone/offset at the same time.

Examples

Assume the time zone is America/Denver.

Source line Converted value
DTI 1970-01-01T08:00:00
DTIZ 1970-01-01T00:00:00Z
DTIB 19700101T080000456
DTIBSZ 19700101 000000456Z
DTI;-0700 1970-01-01T09:00:00.456-07:00
DTIB;-07:00 19700101T090000.456-0700
DTI 1970-01-01T07:59:59.999
DTI;America/Denver 1969-12-31T16:59:59.999-07:00
DTI2;America/Denver 1969-12-31T16:59:59.99-07:00
DTI1 1970-01-01T08:00:00.0
DTI0 1970-01-01T08:00:00
DTIW;America/Denver 1990-W01-1T00:00:00-07:00
DTIR;America/Denver 1990-001T00:00:00-07:00
DTIR2;America/Denver 1990-001T00:00:00.77-07:00
DTIRBS;+08:00 1990001 150000.777+0800
DTIRBSZ;+08:00 Invalid, Z and UTC offset are conflict.
DTIRWBS Invalid, R and W are conflict.
DTI21 Invalid, 2 and 1 are conflict.

Format for RFC-5322

DTW[W|S|WS][;timezone]

The semicolon (;) is the separator.

The format codes and their corresponding datetime format are shown in the below table:

Format Description
(none) Default format: DD MMM YYYY HH:MM TIMEZONE
W Including week: WWW DD MMM YYYY HH:MM TIMEZONE
S Including second: DD MMM YYYY HH:MM:SS TIMEZONE
WS Including week and second: WWW DD MMM YYYY HH:MM:SS TIMEZONE
timezone Time zone name

Examples

Source line Converted value
DTW 14 Aug 2019 12:24 +0000
DTWW Wed 14 Aug 2019 12:24 +0000
DTWS 14 Aug 2019 12:24:36 +0000
DTWWS Wed 14 Aug 2019 12:24:36 +0000
DTW;Asia/Shanghai 14 Aug 2019 12:24 +0800
DTWW;Asia/Shanghai Wed 14 Aug 2019 12:24 +0800
DTWS;Asia/Shanghai 14 Aug 2019 12:24:36 +0800
DTWWS;Asia/Shanghai Wed 14 Aug 2019 12:24:36 +0800
DTW;+0800 14 Aug 2019 12:24 +0800
DTWW;+0800 Wed 14 Aug 2019 12:24 +0800
DTWS;+0800 14 Aug 2019 12:24:36 +0800
DTWWS;+0800 Wed 14 Aug 2019 12:24:36 +0800

Status Code

Use function STATUS() to get the conversion state for functions ICONV() and OCONV().

Status Code Value Description
0 Convert successfully
1 Invalid input data
2 Invalid conversion code
3 Invalid time zone or UTC offset

The features mentioned in this blog post will be available and documented in UniVerse 11.3.2.

 

 

Mingtao Liu

Mingtao Liu 1 Posts

Mingtao Liu is a Supervisor, Software Engineering on the MV Core Development team in Dalian. He is from Liaoning and enjoys playing basketball, climbing and swimming.

0 Comments

Leave a Comment

Your email address will not be published. Required fields are marked *