CODEWITHSUNDEEP
mysql
user5175034
user5175034

Reputation:

MySQL YEAR Comparison

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

Answers (1)

Nick
Nick

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

Related Questions