Introduction
the field types the RDBMS have to offer for that.
While this is not really wrong, it's at least inconvenient since you as a human who looks at the database can't see at a glance what date or time is meant.
Furthermore it usually requires quite a bit of lower math code if you start comparing or calculating with these timestamps. And it becomes ugly when DST
transitions are involved, and timezones, and ranges...
Actually most problems or bad style are caused by just not RTFM.
There is usually a better function for it.
strtotime('now');
If you know PHP, this actually goes without further explanation. But anyway, strtotime is a quite powerful "magic" function which tries its best to convert either a ISO-8601 formatted date/timestamp, a US formatted date or an english expression into a unix timestamp. So the last part is responsible for understanding something like "tomorrow", "yesterday", "next year", "+3 days" etc. It also understands "now" - but compared to time() it's a bit inefficient.
Re-Formatting using String Operations
$datum = substr ($line, 0, 10);
list($dd, $mm, $yyyy) = explode('.', $datum);
$datestring = $yyyy-$mm-$dd;
$timestamp = strtotime($datestring);
At a first glance, the code does not seem to be wrong. The German notation of a date (e.g. 12.05.2008) is taken from a string, split into parts and then
reassembled as ISO-8601 notation and finally transformed into a unix timestamp with our already known friend strtotime.
But is that really what's happening? Spoiler: nope!
I guess, nobody ever tested this code. It was running in production, writing this timestamp and some more information into a log file, a new file every day.
Looking into such a file, all seemed quite well. There was some number at the beginning which looked like a timestamp, it was increasing by one per second,
so it must be right.
But the timestamp was off. By a lot. Depending on the day of the year you are trying out this code, the timestamp has everything right, apart from the year.
The year is somewhere between two and 43 years off, in the past. Why is that? Well, look again.
The first two lines are kind of fine. They extract the date from a longer string, where it is obviously at a fixed position and then splits that date into
day, month and year. But then it substracts first the month and then the day from the year. So, for the date 1.1.2018 it will be 2018-1-1 = 2016. For
31.12.2018 it is 2018-12-31 = 1975.
This new number is then thrown into strtotime which adds the current month, day, hour, minute and second to the year and returns it as a unix timestamp,
which is actually just a number. This behaviour of strtotime is necessary because a unix timestamp can never be without any part of a date and time. It can
interpret information like "november 3rd", which means to humans "november 3rd this year" - so it adds the year by itself.
Our example is clearly a mistake, one could add double quotes in line 3, but a better way would of course be using either
mktime or DateTime.
Date comparison using strings converted to integers
This one comes with a story. More than a decade ago, one of my first tasks at a new employer was getting into a legacy application and fix a few bugs while at it. I did so, came into contact with the client who at one point told me that (micro)payments didn't work. A phonecall later it turned out that it wasn't the payments but the feature behind it. The application was an e-learning platform which offered certification at the end. You had 24 hours after paying to complete the course and beat the questionnaire. So, a few users complained and when she tested it herself, she could reproduce the problem 100% of all times: They expired earlier than 24 hours, she couldn't pinpoint it to a specific duration but it was clearly happening. So with no error pattern provided, I started trying it out myself and was very surprised, that it worked just fine for me. On my local machine and on production. Tried it multiple times, on different days on the weekend, all fine. I then started to look into the code - which was one special part of legacy code hell (long story) - and after quite a while, I found the piece of code, which was responsible to check the date and time of the purchase and thus verify if it was still valid. It looked (simplified) like this:
$date1 = date("YmdGis", $timestampOfPurchase);
$date2 = date("YmdGis");
$difference = (int)$date2 - (int)$date1;
if ($difference > 1000000) {
$error = "purchase expired";
}
I picked up the phone, called the client and was able to confirm my suspicion. She used to test this by buying the voucher shortly before end of business in
the afternoon and then, as first thing the next morning she tried to use it and always found out that it didn't work - and didn't try the same one
again.
The fix then was actually just two bytes on production (followed by a refactoring of that code).
So, how did I know? Well, it's actually quite obvious that this code must have some flaws because the method of comparison is quite unorthodox. The
developer created a numerical string out of the pieces of the timestamp. So, assuming that we have in German notation "12.6.2018 15:30:12", the script would
produce "20180612153012". Looks like a number, can be used like a number but actually isn't.
The same is being done to the timestamp of purchase and then one is substracted from the other and the resulting number is checked if it's higher than a
million. A million is not arbitrary. If we interpret these two numerical strings as one number each, the 7th and 8th digit will represent the day of month,
followed by 6 more digits, representing hour, minute and second. So, whenever this difference is smaller than a million, it means that day, month and year
haven't changed or the day has changed by just one and the current time is before the time of the previous day - this method has another problem, it doesn't
work with vouchers bought on the last day of a month, but obviously either nobody noticed or compained specifically enough.
But that still does not explain that a voucher bought in the afternoon would be expired the next morning but it can be explained by looking closely how the
strings are composed. The format string of date replaces letters with
actual values from the timestamp. 'Y' for example stands for the year in 4 digit representation. A ISO-8601 representation can be achieved with the format
string "Y-m-d H:i:s". And now look closely, instead of the "H" for hour, we have a "G" there. A glance at the manual tells us, G stands for 24 hour
representation of the hour without leading zeros. This small letter causes the numerical string to be one digit short when it's before 10 o'clock in the
morning. Of course when cast to an integer, the calculated difference is now guaranteed to be much higher, so the voucher looks expired. Fixable until the
end of the current month by replacing G with H in both format strings, but fixing it permanently needed a bigger change:
Difference between timestamps
We have two timestamps, those can be used just as they are for this kind of check - if you can live with being off for up to a few hours in either direction twice a year - on a day with a DST transition.
$secondsInADay = 24 * 60 * 60;
$difference = time() - $timestampOfPurchase;
if ($difference > $secondsInADay) {
$error = "purchase expired";
}
Use DateTime or DateInterval
PHP 5.2 came with DateTime, PHP 5.3 with DateInterval. Both are better suited for the job since these classes cover most of the usual date and time related issues and special cases internally. So our code would look like this:
$d1 = new DateTime($timestampOfPurchase);
$d2 = new DateTime();
$diff = $d2->diff($d1):
if ($diff->days != 0) {
$error = "purchase expired";
}