MySql Date Function

Trong công việc nhiều lúc bạn phải làm việc với những thao tác trong MySql đối với dữ liệu dạng date time, việc hiểu rõ function đối với dạng dữ liệu này gíup bạn chủ động hơn.

Link về dạng dữ liệu này http://dev.mysql.com/doc/refman/5.7/en/datetime.html

Link về những function đối với dạng dữ liệu này https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

• Chú ý khi nhúng string trong câu SQL

Gỉa sử ta có câu SQL so sánh dữ liệu dạng date time như sau :

[]> User.where("created_at < ?", Time.now).last.user_id
  User Load (0.7ms)  SELECT  `t_user`.* FROM `t_user` WHERE (created_at < '2016-08-22 09:31:12.196684')  ORDER BY `t_user`.`user_id` DESC LIMIT 1
=> "v_8odooKXUnGmX8u"
[]> User.where("created_at < '#{Time.now}'").last.user_id
  User Load (0.6ms)  SELECT  `t_user`.* FROM `t_user` WHERE (created_at < '2016-08-22 16:31:19 +0700')  ORDER BY `t_user`.`user_id` DESC LIMIT 1
=> "YKPSUYINqHKjzHvs"
[]> User.where("created_at < '#{Time.now.utc}'").last.user_id
  User Load (0.6ms)  SELECT  `t_user`.* FROM `t_user` WHERE (created_at < '2016-08-22 09:31 UTC')  ORDER BY `t_user`.`user_id` DESC LIMIT 1
=> "v_8odooKXUnGmX8u"

Có thể thấy 3 câu query này viết gần như giống nhau nhưng lại ra kết qủa khác nhau, đơn gỉan dạng dữ liệu date time khi đưa vào record của MySql đều được chuyển thành UTC, do vậy khi nhúng dữ liệu dạng này vào trong câu query cần chuyển sang dạng UTC để so sánh được chính xác.

• Select week day dạng string

Đôi khi bạn phải dùng câu query để chuyển 1 column nào đó chuyển thành ngày trong tuần với giá trị không phải là 0,1,2.. mà là Monday, Tuesday ..., thực ra câu query này không khó, chỉ là 1 tip nhỏ để phần nào giảm thời gian tính toán. Ta sử dụng function WEEKDAY và REPLACE trong câu query này.

[]> day_of_week = "Thứ hai   Thứ ba    Thứ tư    Thứ năm   Thứ sáu   Thứ bảy   Chủ nhật "
[]>User.select("REPLACE(MID('#{day_of_week}', WEEKDAY(created_at) * 10, 10), ' ', '') AS week_day").first.week_day
  User Load (0.4ms)  SELECT  REPLACE(MID('Monday    Tuesday   Wednesday Thursday  Friday    Satuday   Sunday   ', WEEKDAY(created_at) * 10, 10), ' ', '') AS week_day FROM `t_user`  ORDER BY `t_user`.`user_id` ASC LIMIT 1
 => "Thứ tư"

Thực ra đây chỉ là việc cắt 1 đoạn string được nối bởi tên các ngày trong tuần với độ dài là 10 ký tự, sau đó bớt đi phần ký tự trống.

• Tính số lượng ngày làm việc giữa 2 ngày xác định

Cũng tương tự như ví dụ trên, tuy nhiên việc xây dựng bảng map phức tạp hơn, trong trường hợp trên có thể coi là bảng 1 chiều, còn trường hợp này là bảng 2 chiều.

[]> User.select("(5 * (DATEDIFF(updated_at, created_at) DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY(created_at) + WEEKDAY(updated_at) + 1, 1)) AS working_day").first.working_day
  User Load (0.7ms)  SELECT  (5 * (DATEDIFF(updated_at, created_at) DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY(created_at) + WEEKDAY(updated_at) + 1, 1)) AS working_day FROM `t_user`  ORDER BY `t_user`.`user_id` ASC LIMIT 1
 => 2.0

String 0123444401233334012222340111123400012345001234550 có thể được coi như 1 bảng map được xây dựng dựa trên ma trận 2 chiều:

 | M T W T F S S
-|--------------
M| 0 1 2 3 4 4 4
T| 4 0 1 2 3 3 3
W| 3 4 0 1 2 2 2
T| 2 3 4 0 1 1 1
F| 1 2 3 4 0 0 0
S| 1 2 3 4 5 0 0
S| 1 2 3 4 5 5 0

Link tham khảo http://stackoverflow.com/questions/1828948/mysql-function-to-find-the-number-of-working-days-between-two-dates

Do vậy muốn tính số ngày nghỉ thì ta sẽ xây dựng bảng map ngược lại, hoặc cỏ thể dùng DATEDIFF rồi trừ đi số ngày làm việc.

• Kiểm tra trùng lặp đối với khoảng thời gian được lặp lại sau repeat_pattern tuần

Gỉa sử ta có start_date1, end_date1, start_date2, end_date2, việc trùng lặp chỉ xảy ra khi : start_date1 < start_date2 < end_date1 hoặc start_date2 < start_date1 < end_date2.

Đối với việc lặp lại theo repeat_pattern tuần, ta chỉ cần tìm repeat_pattern nhỏ nhất thỏa mãn start_date1 + repeat_pattern.weeks < start_date2 < end_date1 + repeat_pattern.weeks hoặc start_date2 < start_date1 + repeat_pattern.weeks < end_date2.

Việc kiểm tra này thực chất là tìm thời điểm gần nhất xảy ra trùng lặp:

scope :dup_with_repeat_pattern, ->(start_time, end_time, repeat_pattern) do
  where("#{table_name}.order_start_time >= ?", start_time)
    .where sanitize_sql_array(["#{table_name}.order_start_time <= '%s' AND \
      (#{table_name}.order_start_time <= ADDDATE('%s', INTERVAL %s * %s day) OR \
      #{table_name}.order_start_time >= ADDDATE('%s', INTERVAL (%s + 1) * %s DAY))",
        start_time + REPEAT_DURATION, end_time, get_rp_by_start_time(start_time, repeat_pattern),
        repeat_pattern * DAY_OF_WEEK, start_time, get_rp_by_end_time(start_time, repeat_pattern),
        repeat_pattern * DAY_OF_WEEK]).delete "\\"
end

class << self
  def get_rp_by_start_time start_time, repeat_pattern
    sanitize_sql_array ["(IF(CAST(#{table_name}.order_start_time AS TIME) >= cast('%s' AS TIME), \
      DATEDIFF(CAST(CAST(#{table_name}.order_start_time AS DATE) AS CHAR), \
        CAST(CAST('%s' AS DATE) AS CHAR)) DIV %s, \
      (DATEDIFF(CAST(CAST(#{table_name}.order_start_time AS DATE) AS CHAR), \
        CAST(CAST('%s' AS DATE) AS CHAR)) - 1) DIV %s))",
        start_time, start_time, repeat_pattern * DAY_OF_WEEK, start_time, repeat_pattern * DAY_OF_WEEK]
  end

  def get_rp_by_end_time end_time, repeat_pattern
    sanitize_sql_array ["(IF(CAST(#{table_name}.order_end_time AS TIME) >= cast('%s' AS TIME), \
      DATEDIFF(CAST(CAST(#{table_name}.order_end_time AS DATE) AS CHAR), \
        CAST(CAST('%s' AS DATE) AS CHAR)) DIV %s, \
      (DATEDIFF(CAST(CAST(#{table_name}.order_end_time AS DATE) AS CHAR), \
        CAST(CAST('%s' AS DATE) AS CHAR)) - 1) DIV %s))",
        end_time, end_time, repeat_pattern * DAY_OF_WEEK, end_time, repeat_pattern * DAY_OF_WEEK]
  end
end

Tại terminal:

[] > Order.dup_with_repeat_pattern("2016-08-15 10:00:00".to_datetime, "2016-08-15 11:30:00".to_datetime, 1).count
   (0.7ms)  SELECT COUNT(*) FROM `t_order` WHERE (t_order.order_start_time >= '2016-08-15 10:00:00.000000') AND (t_order.order_start_time <= '2017-08-15T10:00:00+00:00' AND         (t_order.order_start_time <= ADDDATE('2016-08-15T11:30:00+00:00', INTERVAL (IF(CAST(t_order.order_start_time AS TIME) >= cast('2016-08-15T10:00:00+00:00' AS TIME),         DATEDIFF(CAST(CAST(t_order.order_start_time AS DATE) AS CHAR),           CAST(CAST('2016-08-15T10:00:00+00:00' AS DATE) AS CHAR)) DIV 7,         (DATEDIFF(CAST(CAST(t_order.order_start_time AS DATE) AS CHAR),           CAST(CAST('2016-08-15T10:00:00+00:00' AS DATE) AS CHAR)) - 1) DIV 7)) * 7 day) OR         t_order.order_start_time >= ADDDATE('2016-08-15T10:00:00+00:00', INTERVAL ((IF(CAST(t_order.order_end_time AS TIME) >= cast('2016-08-15T10:00:00+00:00' AS TIME),         DATEDIFF(CAST(CAST(t_order.order_end_time AS DATE) AS CHAR),           CAST(CAST('2016-08-15T10:00:00+00:00' AS DATE) AS CHAR)) DIV 7,         (DATEDIFF(CAST(CAST(t_order.order_end_time AS DATE) AS CHAR),           CAST(CAST('2016-08-15T10:00:00+00:00' AS DATE) AS CHAR)) - 1) DIV 7)) + 1) * 7 DAY)))
 => 5

Trên thực tế có rất nhiều áp dụng của MySql date function làm cho việc tính toán trong code được nhanh hơn, mình hi vọng sẽ trình bày thêm ở những bài viết sau.

Cảm ơn và hi vọng bài viết gíup đỡ phần nào công việc của bạn.