The natural idea of time is dates: year, month, day and time: hours, minutes, seconds. In some applications, days of the week and fractions of seconds matter, but in all applications, tools are always needed to process time variables and present them in a natural way.
Time is characterized by a place in the space where the event occurs and a point where it can be used. This is a general procedure that determines the need for localization and time zones.
MySQL: data types for time recording
Formally, the data types in the MySQL database management system are five descriptors (date, time, timestamp and year). The developer often prefers the universal option: char (20) - when there is an objective reason to use your own interpretation of the MySQL function date_format () or to provide the necessary conditions for proper sorting.
In this example, a table of options for presenting dates is formed, and two lines are entered into it. The first line contains data in a natural format, and the field "x_timestamp" is absent in the fields to be filled. The second line across all fields uses the MySQL now () function. It should be noted that the specified field is filled in both cases.
Timestamp and its format
This type of time field (timestamp) and the use of the MySQL function date_format () is extremely effective for creating timestamps. This “has nothing to do” with any algorithm. This does not require special delays in presenting a date or time. This is just a timestamp - a point on the line of events.
A field of this type is filled in at the moment the row is created. There is no need to pay attention to it, but you can always rely on any query: MySQL date_format () datetime = time when the row was created. It is important, of course, never to change it. The line is created and the time of its creation is determined only once. You can read, you can’t change.
This solution has many fields of application:
- protocol of dialogue with the visitor;
- Security Log
- tracking employee actions;
- calendar of events;
- mailbox etc.
Time is, first of all, a series of events. Everything related to a web resource striving for excellence, rating growth and real reflection of reality is somehow related to the correct display of time:
- as it is, it is not visible to the user, but is available to the developer;
- in the form as it should be reflected on the web resource, used in the algorithm, taken into account in the document, etc.
Time is multifaceted and depends on the point in space where the event occurred in the context of that point in space where it should be used or displayed.
General meaning of MySQL select date_format ()
A query to retrieve information is the most important phrase in SQL. It is so in demand that it is very important when forming the desired sample to turn its contents into the proper form as quickly as possible, without burdening the algorithm with additional designs.
From this point of view, MySQl date_format () allows you to format any date and time value in the format you need immediately at the time the request is executed.
The table contains two rows and there are two identical fields: "x_timestamp" and "x_datetime". The query accesses these two fields, but indicates their names, bypassing PHP, in the date MySQL format directly.
The format string can be any. The content of the formatting string is translated into the result of the query in the “as is” format with the replacement of key elements of the “% *” format, where the asterisk symbol means the format symbol: year, day, month, hour.
National time format and sorting
Using dates in a standard like MySQL is the best solution for using SQL functionality. There is no problem with sorting, nor with calculations, nor with formatting in the style of MySQL date_format ().
But not in all countries the workweek starts on Sunday, it is not always considered normal to view the time in a 12-hour format, and not all languages have the concepts of "AM" and "PM". There are many such national features, but the vast majority of them lie beyond the functionality of the SQL language.
It is doubtful that in general there is such a dialect of SQL that can expand to take into account the national colors of countries further than beyond the limits of the term "localization". Actually, it is not surprising that "localization" in practice means more than the automatic replacement of some words (combinations) by others, depending on the language.
But not always the national language determines everything, first of all, it has nothing to do with the correct representation of time.
Event Key and Data Key
Time never goes back, therefore, it is an ideal key to any row, to any table, to any database. Two events can occur simultaneously if their tracking interval is longer than the time difference between their occurrence. For example, two users visited the site at the same minute, but at different seconds of that minute.
PHP has an interesting solution: there is an object and there is an instance of the object. There is an idea of a static "variable":
- private static $ iUniqueNo = -1; // "UU" is always a unique number
- // each time this function is called, $ iUniqueNo will be different and
- // within one second will never happen again.
- public function IncUniqueNo () {
- if (self :: $ iUniqueNo> 99)
- self :: $ iUniqueNo = 0;
- else
- self :: $ iUniqueNo ++;
- }
Without too much imagination, merging into a single whole: year, month, day, hours, minutes, seconds and $ iUniqueNo, we get: YYYYMMDDhchmmssUU - a unique key ("UU" = "$ iUniqueNo") for any record and at the same time a time stamp for any use.
This is an ideal solution in the context of the database time type and the MySQL function date_format (), which simply does both time and key from the same "timestamp" field at the same time.