CODEWITHSUNDEEP
mysql
Farid Movsumov
Farid Movsumov

Reputation: 12725

MySQL WEEK(FROM_UNIXTIME()) gives wrong result

WEEK(FROM_UNIXTIME(1525212000))

gives result as 17 but it should be 18

Is it bug in MySQL or I am doing something wrong?

mysql Ver 14.14 Distrib 5.7.18

Example Query

enter image description here

Timestamp to date online

enter image description here

Checking Date with online converer

enter image description here

Upvotes: 0

Views: 237

Answers (2)

Farid Movsumov
Farid Movsumov

Reputation: 12725

I decided it would be best option to use WEEKOFYEAR function instead of WEEK function

WEEKOFYEAR(date) Returns the calendar week of the date as a number in the range from 1 to 53. WEEKOFYEAR() is a compatibility function that is equivalent to WEEK(date,3).

Source: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_weekofyear

Upvotes: 1

xander
xander

Reputation: 1765

The week can be in the range of 0-53, so you just need to add 1 to the result or better yet change the mode how weeks are counted with the second parameter to the week function.

WEEK(date[,mode])

Look at the official documention of the week function: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_week

If the mode argument is omitted, the value of the default_week_format system variable is used

Upvotes: 1

Related Questions