# 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)