Managing Date and Time Fields in SQL

In SQL date, and time fields are converted to type the eXtremeDB type datetime for all operations. The only difference is how they are formatted for output. So, for example a date field day could be incremented day+5 and the result will be of type date.

For an overview see page Date, Time and Datetime Fields

The date, time and datetime types are represented internally as integer values. When arithmetic is performed, the result will be of the original type, but the underlying value is used to perform arithmetic. So the datetime precision can be important. The default precision for xSQL is 1, i.e. 1 second. But it can be changed by the setDateTimePrecision() function. Regardless whether type date or datetime is used, seconds (or milliseconds, etc.) will be used in the operation.

For example, consider the following operations:

 
    XSQL>select cast('02-02-2013' as date);
    #1
    ------------------------------------------------------------------------------
    02/02/2013 00:00:00
 
    Selected records: 1
    XSQL>select cast('02-02-2013' as time);
    #1
    ------------------------------------------------------------------------------
    02/02/2013 00:00:00
 
    Selected records: 1
    XSQL>select cast('02-02-2013' as date) + 5;
    #1
    ------------------------------------------------------------------------------
    02/02/2013 00:00:05
 
    Selected records: 1
    XSQL>select setDateTimePrecision(1000);
    #1
    ------------------------------------------------------------------------------
    1
 
    Selected records: 1
    XSQL>select cast('02-02-2013' as date) + 5;
    #1
    ------------------------------------------------------------------------------
    02/02/2013 00:00:00.005
     
    Selected records: 1
     

 

Inserting Date and Time Data

Data can be inserted into date, time and datetime fields by specifying the date and time in a variety of string formats. (Currently on Windows systems, valid date values must have the year specified as greater than 1969. (Note that on Unix-Linux platforms, and in the next release for Windows, the strptime() function will be used internally which does not have this limitation.)

For example consider the following table:

     
    XSQL>create table dt(d date, t time);
     

Date and time values can be inserted with a string formatted "M/D/Y h:m:s". If the value of M is greater than 1900, then the string is considered as "Y/M/D h:m:s". For example:

     
    XSQL>insert into dt values( '7/10/1970 15:15:15', '23:23:23');
    XSQL>insert into dt values( '1970/7/30', '0');
    XSQL>select * from dt;
    d       t
    ---------------------------------------------------------------
    07/10/1970 15:15:15     23:23:23
    07/30/1970 00:00:00     00:00:00
     

Insert strings can also use the hyphen or dot separators, instead of slashes, for dates. For example:

 
    XSQL>insert into dt(d) values( '6-10-1970');
    XSQL>insert into dt(d) values( '1970.7.30');
    XSQL>select * from dt;
    d       t
    ---------------------------------------------------------------
    06/10/1970 00:00:00     00:00:00
    07/30/1970 00:00:00     00:00:00
     

Another alternative is to use abbreviations (first three letters) for the month with hyphen separators; i.e. a string with format "D-MON-Y h:m:s". For example:

 
    XSQL>insert into dt(d) values( '30-sep-1970');
    XSQL>select * from dt;
    d       t
    ---------------------------------------------------------------
    09/30/1970 00:00:00     null
 

Note that the month integer value M must be a month number starting from 1 for January, and the year value Y can be 4 digit integer greater than 1900; otherwise, if the value of Y is less than 50 it is considered as a 2000+ year, if greater than or equal to 50 it is considered a 1900+ year. For example:

 
    XSQL>insert into dt(d) values( '30-aug-20');
    XSQL>insert into dt(d) values( '30-aug-80');
    XSQL>select * from dt;
    d       t
    ---------------------------------------------------------------
    08/30/2020 00:00:00     null
    08/30/1980 00:00:00     null
     

The hour h and minute m values must be integers less than 24 and 60 respectively and seconds s can be expressed as an integer or floating point value, however floating point fractional values are truncated. For example:

 
    XSQL>insert into dt(t) values( '23:59:19.6');
    XSQL>select * from dt;
    d       t
    ---------------------------------------------------------------
    null    23:59:19
     

If a time part is not supplied, the string is considered as a date and the time part is set as zero. If the string could not be successfully parsed as a date, then the time format "h:m:s" is tried.

 

Storing and Manipulating High-definition Datetime Data

When using high-definition data, for instance timestamp fields with millisecond granularity, it is recommended to use the bigint data type. It is possible to use a char(9) type field to store a timestamp, for example

 
    create table DailyQuotesIN (tTime char(9), ...)
     

The eXtremeSQL runtime will implicitly perform the conversion from a char(9) to an integer -- no explicit casts of input data are required. However, from the performance standpoint it is preferable to use the bigint data type:

 
    create table DailyQuotesIN (tTime bigint, ...)
     

Using the bigint data type you would simply load tTime data with the time encoded as an integer value like 093515984593000 and perform any manipulations with this data directly, including various forms of output. Note however that to extract some part of the field representing a time (for example the hour or minute), standard SQL functions should not be used; i.e. do not use the SQL function hour() or minute(). Instead use integer division. For example:

 
    select tTime / 10000000000 as hour, 
        tTime / 100000000 % 100 as minute, 
        real(tTime % 10000000000) / 100000000 as second 
    from DailyQuotesIN;
         

 

Converting Date to Integer

When using variables of type Date it can be useful to convert the Date value to a long integer value representing the number of milliseconds since January 1, 1970, 00:00:00 GMT. This can be done by using the Date method getTime(). The following code snippet demonstrates how this method might be used (in Java):

 
    {
        Calendar cal = Calendar.getInstance();
        cal.set(2014,  1,  1,  9, 30);
        cal.set(2014, 12, 30, 16, 00);
        Date dstart = cal.getTime();
        Date dfinish = cal.getTime();
        long start = dstart.getTime();
        long finish = dfinish.getTime();
 
        System.out.println("Calculating high bids over symbols from time " + start + " to " + finish + ":");
        SqlResultSet res = conn.executeQuery("SELECT Symbol, seq_ignore(seq_search(qTime, ?, ?)) as t, 
                                seq_max (Bid@t) FROM Quotes", start, finish);
                                 

Implementation details

Given the embedded nature of the eXtremeDB, the datetime core data type is stored as a 8-byte integer and allows all integer operations such as addition and subtraction. The datetime type is interpreted by applications, and could represent for example the epoch time in seconds, or the time in CPU ticks since the device was turned on, etc., That said, the higher-level eXtremeDB components such as Java and Python wrapper APIs, SQL API and xSQL server assume that the datetime type is interpreted as a UTC timestamp: the UNIX epoch time.

Epoch, by definition, is a point in time that is used as a reference point for measuring time. It doesn’t necessarily have to have a specific meaning. January, 1 1970 known as the Unix epoch time is used by many Unix-like systems and programming languages, such as C/C++, Java, JavaScript, Perl, PHP, Python, etc. The Unix epoch 0 is at 00:00:00 UTC on 1 January 1970, so systems use a negative number to represent any timestamp before that.

Since Unix time increments every second, and as an alternative to using a different data type to represent time with greater precision, the eXtremeDB adds a property to the datetime type called "datetime precision”. The precision property numerically represents the number of ticks per second. The default value for it is 1, which means that the database stores a datetime value as the number of seconds since the epoch (i.e. 00:00:00 UTC 1 January 1970). However the resolution of datetime fields can be changed by an application to a resolution ranging from 1 (to store the number of seconds) to 1000000000 (for the number of nanoseconds).

For example the epoch time for the "2023-07-04 12:00:00 GMT” date is equal to 1688472000. If the dt_precision is set to 1000 indicating the milliseconds resolution, the eXtremeDB database would represent it as 1.688.472.000.000

The algorithm used by the SQL INSERT operator is as follows:

The date string is parsed into the C language time structure that contains a calendar date and time broken down into its components. The structure contains nine members of type int (in any order), which are:

Member
Type
Meaning
tm_sec
int
seconds after the minute
tm_min
int
minutes after the hour
tm_hour
int
hours since midnight
tm_mday
int
day of the month
tm_mon
int
months since January
tm_year
int
years since
tm_wday
int
days since Sunday
tm_yday
int
days since January 1
tm_isdst
int
Daylight Saving Time

The eXtremeDB sets the tm_isdst to -1 indicating that the Daylight Saving Time information is not available. The tm structure is then passed to the C runtime mktime() function that converts the time into the UNIX epoch representation in the current time zone (all time zones are defined as offsets from UTC ). Finally the value is multiplied by the "datetime precision”.

Conversely, when the SQL engine reconstructs the stored datetime value back into string, the stored value is divided by the set precision, receiving the epoch time in seconds. Then the epoch time is passed into the C runtime localtime() function that fills out the tm strcuture in the current time zone and the tm structure values are then represented in a form of a formatted string.

Even though most systems in the world use Unix time, it has limitations. On some embedded operating systems such as QNX 7.0, the mktime() function doesn't support dates below 01/01/1970 (i.e. does not work with negative epoch time). In these cases the eXtremeDB makes use of the time-shift technique that essentially up-shifts all dates below the year 1970 to a predefine number of years, then converts the dates through the mktime() and then downshifts the value back prior to storing it in the database. Thus the database runtime keeps all dates passed 1970 as the epoch_time * datetime_precision value while dates prior to 1970 are kept as negative numbers. When applications read dates back, the process is reversed. Note that these values could be off from the epoch time by the date light saving time value (usually one hour) because of different daylight saving rules applied in different calendar years. However, the conversion from the input string into the internal storage representation and back into the string is preserved.

Furthermore, some applications benefit from interpreting datetime as intervals in seconds/milliseconds/nanoseconds as opposed to absolute dates. In this case the stored 8-byte value would be formatted as hours:minutes:seconds. In order to accommodate these scenarios it is possible to define an interval from 1/1/1970 forward within which the stored datetime translates to time interval. By default the interval value is zero (all dates are viewed as calendar dates). The SQL function setMaxInterval(n_sec) or the xSQL command maxinterval n_sec are used to adjust the length of the interval. For example maxinterval 86400 instructs the xSQL to interpret all dates 24 hours forward and back from the 1/1/1970 as time interval (note that the referenced values could be negative, which is helpful when arithmetic operations over time values are performed)

Send feedback on this topic to McObject.Download documentation.© 2001-2024 McObject LLC. All rights reserved.