As I have already said in another answer:
Dates have no format
Dates and times are just concepts, ideas: a date represents a specific point in the calendar, a time represents a specific moment of a day.
The date/time of "March 11, 2019 at 11:08:39" represents two ideas:
- the date represents a specific point of the calendar, the 11th of March of the year 2019.
- the time represents a specific point of this day: 11 hours (morning), 8 minutes and 39 seconds.
To express these ideas in text form, I can write them in different ways:
- 11/03/2019 11:08:39 (a common format in many countries, including Brazil)
- 3/15/1990 11:08:39 AM (American format, reversing day and month and using AM for hours)
- 2019-03-11T11:08:39 (The format ISO 8601)
- 11 March 2019, 11h08 (in good Portuguese)
- March 11th, 2019, 11:08 AM (in English)
- 2019 年 3 月 11 日 11 時 08 分 39 秒 (in Japanese)
- and many others...
Note that each of the above formats is different, but all represent the same date and time (the same numerical values of the day, month, year, hour, minute and second).
When you save a date and time in the database, using the available types (be timestamp, or datetime, or date, or whatever), the bank stores these values and that’s it. The format in which this information is there is internal implementation detail and it doesn’t matter.
When you make a SELECT in the database, or shows these values on the screen with echo, or write to a log file, or whatever, obviously these values will be displayed in some format. But that doesn’t mean the date/time is necessarily in that format.
What type to use? Depends
According to the documentation Mysql for date/time types, we have:
DATE: keeps only the day, month and year. If you nay accurate schedules, is a good choice (for example, to store only the date of birth of users in a simple register - but if it is a hospital or notary system, for example, or any other context that needs the time, then it is no longer indicated to use it).DATETIME and TIMESTAMP: both have the date and time. The difference is that TIMESTAMP converts the current Timezone date/time (the one used on the connection) to UTC when the date is saved, and converts back (from UTC to the current Timezone) when querying the data (see documentation for more details - I particularly find these conversions very confusing).
I’m not really familiar with the functions mysqli_xxx, but as I recall, even dates are returned as strings (so you get the impression that dates are in a certain format). But remember that this is just a representation of the values stored in the bank.
But a DATETIME It can still be ambiguous, depending on what you need. For example, "March 11, 2019 at 11 am" occurred at a different time in each part of the world (in Australia this date and time occurred several hours before Brazil, for example). If you want to know exactly at what time a certain event occurred, it is best to use a timestamp (not the Mysql type, but the numerical value representing the number of seconds since then Unix Epoch).
In Mysql we can use UNIX_TIMESTAMP() to obtain this value:
mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
| 1552350656 |
+------------------+
The value returned is the number of seconds since the Unix Epoch and can be stored in a numerical field. The advantage of being a number is that it can be easily compared to others (the higher the value, the more in the future is the timestamp).
The detail is that this value (1552350656) represents a single instant (a point in the timeline), which is the same all over the world. But in each time zone it can correspond to a different date and time. The above value, for example, corresponds to 11/03/2019 at 21:30 in São Paulo, but the 12/03/2019 at 09:30 in Tokyo. If you are going to show it on the screen, remember to convert to the correct Timezone. Example:
// criar data a partir do timestamp
$data = new DateTime('@'. 1552350656);
// converter para um timezone
$data->setTimeZone(new DateTimeZone('America/Sao_Paulo'));
// mostrar em algum formato
echo $data->format('d/m/Y H:i:s'); // 11/03/2019 21:30:56
In case you receive a string, use createFromFormat:
$data = DateTime::createFromFormat('Y-m-d H:i:s', '2019-03-11 11:08:39');
In this case, since I didn’t set Timezone, the date will already have the values indicated in the string (using Timezone default that is configured in PHP).
By manipulating dates as instances of DateTime, it is possible to compare them and calculate the difference between them:
$data2 = new DateTime();
if($data < $data2) {
var_dump($data2->diff($data));
}
The method diff returns a DateInterval, which has detailed information on the difference between the dates (how many years, months, days, hours, minutes and seconds there are between them, in separate fields).
To add/subtract durations to a date, you can use the methods add and sub. Both receive a DateInterval as a parameter:
$data = new DateTime('@'. 1552350656);
// somar 30 minutos, usando o formato ISO 8601 (https://en.wikipedia.org/wiki/ISO_8601#Durations)
$data->add(new DateInterval('PT30M'));
// ou usando os formatos relativos (http://de2.php.net/manual/en/datetime.formats.relative.php)
$data->add(DateInterval::createFromDateString('30 minutes'));
You can use both the format ISO 8601 (PT30M is equivalent to a duration of 30 minutes), as the relative formats (like "30 minutes").
In short
Working with dates is a broad subject, but in general PHP already has specific types with various features to compare, calculate difference, format, etc. The same thing goes for the database.
If your code is receiving strings, convert them to dates and do the proper manipulations (comparisons, date arithmetic, etc.) using the specific types (DateTime, for example). Only convert dates to strings (converting them to a specific format) when necessary (whether to show to the user, write to a log file, or for any operation that requires a string).