Date and Time

Date and time

The management and administration of temporal values is the part of the DBMS less standarized and with more differences between them. Besides, and with the character codification and region features (e. g. date formats), it is part of a set of global parameters of the system that the administrator must understand and take into account depending on the environment where the results will be shown. If it would be part of a set of dinamic web pages, not only the server must be properly configurated but also the http server, the hosted language (e.g. php) or even the clients must be taking into account. It is very probably that the same date, in SQL Developer, shown in a result frame or in an output script will have a different format (the first one is controled by a client program and the second shows the data the same way are sended by the server.

Therefore, here the date issues will be treated in a shallow level and always from the SQL query point of view.

MariaDB/MySQL offers various types related with time:

• • DATETIME     '0000-00-00 00:00:00'

  • DATE     '0000-00-00'

  • TIMESTAMP     00000000000000

  • TIME     '00:00:00'

  • YEAR     0000

What is shown in the previous list gives us an idea of what type of data and result manages every type. Actually, the previous list is a relation of "zero values" that can every type can stored, being their default values. Leaving the type TIMESTAMP aside, that has its own properties and applications, all the types are related and differ from the others in storing limitations and automatic type conversions.

Strings, numbers, dates and times

Dates, except for the use of functions mentioned below, are treated the same as strings. In fact, constants are also enclosed in quotation marks. Therefore, they can be used in comparisons of equality, inequality, greater than, etc. 

Note that a date type is being compared with a character string. MariaDB/MySQL analyzes the string and determines if it has a appropriate format for the data type and it proccess it if so. In fact, although the generic output of a date is always yyyy-mm-dd, the character string we use for the comparison has some format freedom:

ingreso < '1990@01@01'

ingreso < '1990/01/01'

ingreso < '1990.01.01'

ingreso < '1990:01:01'

In all the cases the results in identical to the preceding. 

Another aspect with some freedom is the format of the year:

select *

from profesores

where ingreso < '90-01-01';

Now, you have to take into account that MariaDB/MySQL makes an interpretation of that part of the date in such a way that years in the range 00-69 are converted into 2000-2069, and 70-99 into 1970-1999. If we handle earlier or later years we must use the 4 characters.

Date and time functions

The set of functions offered by each product —MariaBD, MySQL, SQL Server, Oracle Database, etc.— is usually specific to each one, even if, in the end, they offer more or less the same operation. This is most evident in the date and time functions, which are highly dependent on how the storage of this type of data is managed in each brand of database server. It is also true that, apart from minimal changes in the names and parameters of the functions - and somewhat less in the formats - a core of standard functions is becoming more and more consensual. 

In the reference manual of  MariaDB is possible to look for the complete functionality related to time management. Here we show some of them:

now(), curdate(), curtime()

date_format(), str_to_date()

day(), dayofweek(), dayname(), month(), year(), hour(), minute(), second()

Format

This is to show or transform the date format. Main format functions are 

• date_format() 

• str_to_date(). 

Recall that the type date, or time, is not a text string, it has a special storage, even if we write it in quotes. One is the inverse of the other: dateformat() transforms the date to text, and str_to_date() a string to date type. Both work with 2 parameters, an expression and a format string. The format string tells the function what the data we supply in the first parameter looks like or what we want it to look like, depending on whether it is one or the other function:

In this case we do the opposite, and supply a format string so that MariaDB/MySQL can correctly interpret the text value as a valid date:

For whatever reason —think of a computer program accessing the server— the date we need to query is in a special format, "day/month/year with 2 digits". The first parameter is the date, and the second is the format in which MariaDB/MySQL must read the previous one. This way, MariaDB/MySQL knows how to transform it to its native format, '1990-01-01', and compare with those stored in the table.

Thus, the format string for the first query represents "how we want the output" and the one for the second query represents "how the date I want to compare is written". 

Extraction

The other interesting functions are the ones that extract part of a temporal expression. In all these examples we are going to work with the day, month or year of the dates, and with several types of possible information.

Of course, you can do much more with dates and times —difference in days between two dates, for example— just go to the MariaDB manual or to the many examples that can be found in a simple search.