MySQL provides the developer with the ability to work over time in a variety of ways. There are data types for describing fields in tables. Many features are available for use in queries. Time localization and change of time zones in sessions, MySQL settings and use of operating system time are available.
Responsibility for applying data types and changing time zones lies with the developer, who must consider client time and server time. They do not always match.
Description of tables with time fields
The example describes three options for representing time. Two are of type MySQL timestamp (start_timestamp, next_timestamp), one is of type MySQL datetime (start_datetime) and the user option is a character string (work_income).
The timestamp type is a MySQL-native format for capturing events; it occupies 8 bytes, is an integer and has one feature: the first column is filled in automatically at the time of creation: query (1). The second and all of the following remain null, but it is not NULL. Query (2) contains the MySQL function timestamp now () used to populate the start_datetime field.
This example shows that the table formed by the query (1, table creation) will automatically fill the first column start_timestamp when creating the table. The query (2, filling in the table) shows that this is exactly so. In this case, the second column next_timestamp remains zero: all components of the date and time are represented by "0" in all positions, but not NULL, as the whole field.
If we remove from the request a reference to the start_datetime field and the function that fills it with now (), then the start_datetime field will contain a NULL value. The upper part of the image shows the table using the fill function, the lower part shows the table without using now (). The differences are significant.
Unix time and user time
The fields in the MySQL timestamp & datetime table are different in filling and usage. For example, the option:
- int mktime ([int hour [, int minute [, int second [, ...
available for a field of type datetime, while a field of type timestamp focuses more on functions of the MySQL language itself.
In order to use the time fields most effectively and safely, it is advisable to focus on the method of working with MySQL timestamp in the proposed functionality, in its purpose, which is determined by the database itself. This is her own stamp of a moment in time, a format for representing time.
The datetime type is more relevant to time in the representation of the Unix era and can be associated, for example, with the PHP function mktime () or the usual text format, for example:
Here the variable $ cStndDate is plain text, which is interpreted in the request as something written in the date / time format. If the variable is specified correctly, then the desired value is written to the desired field of the table.
User time is a requirement of the task. It is far from always necessary to present all rights to time management to the database. She will always do everything "in her own way." This is not always correct, for example, the names of Russian months or a special procedure for recording the day, month and year are needed.
Time Zones and the Right Logic
Working with MySQL timestamp & datetime requires care and accuracy. Another thing is its own date format, written in plain text. True, in this case, the advantages of standard date types are lost, for example, sorting or calculating over dates, but the desired advantages appear.
In any case, the developer should be extremely careful. Time zones, localization settings and many other nuances can create a certain misunderstanding. If something when working with a date has become with NULL or zeros in all positions, you need to look, first of all, for an error in your own algorithm. MySQL, its date fields and time functions work exceptionally stably and correctly.