Reputation:
Years ago, I wrote a query and now while going back through some of this old code I'm trying to figure out why I did it the way it had been done. The intent, as far as I can tell, was to simply extract the year from a UNIX timestamp. Can anyone tell me what the benefit might have been to use:
YEAR(DATE_ADD(DATE_SUB(DATE_ADD(FROM_UNIXTIME( 0 ), INTERVAL FullDate SECOND), INTERVAL @@session.time_zone HOUR_MINUTE), INTERVAL '08:00' HOUR_MINUTE))
Instead of simply:
DATE_FORMAT(FROM_UNIXTIME(FullDate), '%Y')
Upvotes: 1
Views: 49
Reputation: 147286
It looks like you were trying to compensate for whatever the current time zone was and then output the (year of the) current time in the timezone UTC+8:00
(countries this applies to from Wikipedia).
So DATE_ADD(FROM_UNIXTIME( 0 ), INTERVAL FullDate SECOND)
gives you the current time, in the current time zone; the DATE_SUB(..., INTERVAL @@session.time_zone HOUR_MINUTE)
converts that to UTC, and DATE_ADD(..., INTERVAL '08:00' HOUR_MINUTE)
then converts the time to UTC+8:00
.
If you didn't want to make that timezone conversion, then DATE_FORMAT(FROM_UNIXTIME(FullDate), '%Y')
will do the job, as in fact would FROM_UNIXTIME(FullDate, '%Y')
or YEAR(FROM_UNIXTIME(FullDate))
Upvotes: 0