Select * from table where DATE_FORMAT (FROM_UNIXTIME (createtime), '% Y-% m-% D') = '2017-01-02' FROM _ UNIXTIME use syntax
FROM_UNIXTIME (unix_timestamp, format) FROM_UNIXTIME (unix_timestamp), FROM_UNIXTIME (unix_timestamp, format) is a time function in MySQL. unix_timestamp can be a field name or Unix time, format is mainly used to format the returned value. If the format is provided, the format of the result is determined by the format string. Returns a string representing the Unix time mark, formatted according to the format string. Format can contain the same modifier as the entries listed by the DATE_FORMAT () function. Format the date value based on the format string.
The following modifier can be used in the format string: % M month name (January ...... December) % W name of the Week (Sunday ...... Saturday) % D Date of the month with an English prefix (1st, 2nd, 3rd, and so on .) % Y year, number, 4 digits % y year, number, 2 digits % a abbreviation of the week name (Sun ...... Sat) % d number of days in the month (00 ...... 31) % e number of days in the month (0 ...... 31) % m month, number (01 ...... 12) % c month, number (1 ...... 12) % B abbreviated month name (Jan ...... Dec) % j days in a year (001 ...... 366) % H hour (00 ...... 23) % k hour (0 ...... 23) % h hour (01 ...... 12) % I hour (01 ...... 12) % l hour (1 ...... 12) % I minute, number (00 ...... 59) % r time, 12 hours (hh: mm: ss [AP] M) % T time, 24 hours (hh: mm: ss) % S seconds (00 ...... 59) % s seconds (00 ...... 59) % p AM or PM % w days in a week (0 = Sunday ...... 6 = Saturday) % U Week (0 ...... 52), here Sunday is the first day of the week % u Week (0 ...... 52), Monday is the first day of the week % A text "%"