Ask Reuben
Datetime and Interval
How do I convert between various DATETIME and INTERVAL datatypes?
How can I alter the display format of a DATETIME or INTERVAL?
Why can’t I assign an INTERVAL to an INTEGER?
Why are INTERVAL’s split into two syntaxes?
How do I convert between various DATETIME and INTERVAL datatypes?
When dealing with DATETIME the key thing to keep in mind is the normalized format of YYYY-MM-DD hh:mm:ss.fffff. Similarly with INTERVAL the normalized format of YYYY-MM or DD hh:mm:ss.fffff. This flows through to the concept of Datetime literal and Interval literals that use the same normalized format.
As per the data type conversion refererence, a datetime or interval will be converted to one of the character types (CHAR, VARCHAR, STRING) by using this format and vice versa, a character type can be converted to a datetime or interval if it matches this normalized format.
As a little exercise you can do something like …
DEFINE dt DATETIME YEAR TO SECOND DEFINE s STRING -- Assign as DATETIME literal LET dt = DATETIME(2020-03-17 12:34:56) YEAR TO SECOND DISPLAY dt -- Convert string to datetime LET dt = "2020-03-17 12:34:56" DISPLAY dt LET dt = CURRENT DISPLAY dt -- Convert string to datetime LET s = dt DISPLAY s
… and see that you can assign a datetime either as a literal or as a string in the normalised format. Similarly if you display a DATETIME, this will be the format it is written to a string as. You can do a similar exercise with INTERVAL.
The EXTEND operator can be used to convert a datetime precision with a different precision.
How can I alter the string format of a DATETIME or INTERVAL?
What you may not be aware of is that there are methods that enable you to format and parse DATETIME and INTERVAL using different formats.
In recent Genero versions, a util.Datetime class (in version 2.51) and a util.Interval class (in version 3.00) were added. Both classes contain format and parse methods that allow you to work with different formats for both DATETIME and INTERVAL datatypes.
The util.Datetime.format and util.Interval.format methods enable you to output and display DATETIME and INTERVAL variables in different formats using a format string. This format string is similar for both datetime and interval.
You can experiment with these format strings by trying code like …
DISPLAY util.Datetime.format(CURRENT, "(%a.) %b. %d, %Y")
… a one important to note is that the format string is case sensitive. So %m is the code for months whilst %M is the code for minutes.
The util.Datetime.parse and util.Interval.parse methods work in the other direction, and enable you to take a datetime or interval that is expressed as a character/string datatype written in a different format, and parse it so that it can be assigned to our datetime and interval datatype. Very handy when inputting data from different systems who might not use the normalized format.
If you have not seen these methods before, you may have some existing Genero library code that can be modified and simplified to use these methods instead
Why can’t I assign an INTERVAL to an INTEGER?
Confusion can arise when you have INTERVAL datatypes that consists of a single qualifier. By that I mean something like INTERVAL MONTH TO MONTH, INTERVAL MINUTE TO MINUTE etc.
You can’t normally assign an INTERVAL to an INTEGER because of the : and – characters typically seen in an INTERVAL. With these single qualifiers there are none of these special characters and so on the face of it, it looks like it should be something you can assign to a numeric datatype.
If you attempt to assign an interval to a numeric datatype e.g.
DEFINE l_interval INTERVAL SECOND(9) TO SECOND DEFINE l_integer INTEGER LET l_interval = INTERVAL(1) SECOND TO SECOND LET l_integer = l_interval
… you will get a runtime error on the assignment of
FORMS statement error number -1260. It is not possible to convert between the specified types.
Unfortunately the datatype conversion rules don’t have an exception for this case and the conversion logic falls out on the bottom entry of that conversion reference page.
Other data type conversions not mentioned in this topic are not allowed and will result in a runtime error.
Our code is doing what is documented and is following what Informix-4gl did before us.
What you can do in this case is to use the format method in the util.Interval class.
LET l_integer = util.Interval.format(l_interval, "%S")
and this will allow the assignment in one line of code.
Why are INTERVAL’s split into two syntaxes?
If you read the INTERVAL page, the first sentence reads …
The INTERVAL data type stores spans of time as Year/Month or Day/Hour/Minute/Second/Fraction units.
… and from there it refers to two different types of syntaxes. Why the distinction between Year/Month and Day/Hour/Minute/Second/Fraction units, and why the two syntaxes?
One answer would be because this is what Informix-4gl did, and what Informix databases, and is also in the ANSI SQL-92 standard. However the better explanation is to ask yourself how many months in a year?, how many days in a month?, how many hours in a day?, how many minutes in an hour, how many seconds in a minute?
The answers for all of these are constants, except one, “how many days in a month?”. This fact means you can’t add and convert across this divide. This can also be illustrated with
DEFINE i1 INTERVAL SECOND(9) TO SECOND DEFINE i2 INTERVAL MINUTE TO MINUTE DEFINE i3 INTERVAL DAY(9) TO DAY DEFINE i4 INTERVAL MONTH TO MONTH DEFINE i5 INTERVAL MONTH(9) TO MONTH DEFINE i6 INTERVAL YEAR TO YEAR LET i1 = INTERVAL(1) SECOND TO SECOND LET i2 = 120 * i1 DISPLAY i2 LET i3 = INTERVAL(1) DAY TO DAY LET i4 = 120 *i3 DISPLAY i4 LET i5 = INTERVAL(1) MONTH TO MONTH LET i6 = 120 *i5 DISPLAY i6
120 seconds can be summed together to form 2 minutes, 120 months can be summed together to form 10 years, but if you sum 120 days together, how many months is that?
Widgets
Final point on DATETIME and INTERVAL. Historically there has been no widget that is used for a DATETIME and so an EDIT or LABEL was used. Version 2.51 saw the introduction of the DATETIMEEDIT widget. I still see some DATETIME variables being input through an EDIT or through two EDIT widgets (date and time portions split). if you are still doing this, review your code to consider using the DATETIMEEDIT widget.
There is no equivalent to the DATETIMEEDIT widget for INTERVAL’s. Unfortunately the native widgets available don’t normally cater for the fact that an interval can have more than 12 months, or more than 31 days, or more than 24 hours, or more than 60 minutes or more than 60 seconds.
That will be the final final point, just remember that first part of an INTERVAL can be larger than what you might think. If you note the syntax to define an INTERVAL, that first qualifier allows you to define a precision to cater for the maximum value, that is instead of INTERVAL HOUR TO SECOND, define as INTERVAL HOUR(5) TO SECOND. It is a good habit to always assign that first precision rather than relying on the default of 4 for YEAR and 2 for the other qualifiers.