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
[]> 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ư"
1 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
[]> 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ư"
3 của MySql đều được chuyển thành
[]> 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ư"
5, do vậy khi nhúng dữ liệu dạng này vào trong câu
[]> 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ư"
1 cần chuyển sang dạng
[]> 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ư"
5 để so sánh được chính xác. - Select week day dạng string
Đôi khi bạn phải dùng câu
[]> 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ư"
1 để chuyển 1
[]> 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ư"
9 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
[]> 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ư"
1 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
[]> 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
2 và
[]> 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
3 trong câu
[]> 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ư"
1 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
[]> 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
5 đượ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
[]> 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
6 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
[]> 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
7 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ó
[]> 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
8, việc trùng lặp chỉ xảy ra khi :
[]> 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
9 hoặc
| 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
0. Đối với việc lặp lại theo
| 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
1 tuần, ta chỉ cần tìm
| 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
1 nhỏ nhất thỏa mãn
| 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
3 hoặc
| 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
4. 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
| 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
5 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. | 
