So sánh thời gian của MySQL trong mệnh đề WHERE

Question

Trong hướng dẫn này, bạn đã tìm hiểu về kiểu dữ liệu

CREATE TABLE timestamp_n_datetime (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ts TIMESTAMP,
    dt DATETIME
);
 Nội dung chính Show
 Truy vấn so sánh ngày trong SQL
Giải pháp 2 - So sánh ngày với mệnh đề between
Luôn sử dụng >= và < để so sánh ngày tháng
Làm cách nào để so sánh hai ngày trong mệnh đề WHERE trong SQL?
Làm cách nào để so sánh hai dấu thời gian trong MySQL?
Làm cách nào để sử dụng mệnh đề WHERE cho ngày trong MySQL?
Làm cách nào để so sánh hai trường ngày trong MySQL?
 
Code language: SQL (Structured Query Language) (sql)

0 của MySQL và một số hàm

CREATE TABLE timestamp_n_datetime (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ts TIMESTAMP,
    dt DATETIME
);
Code language: SQL (Structured Query Language) (sql)

0 hữu ích

Một giá trị ngày, giờ hoặc dấu thời gian có thể được so sánh với một giá trị khác của cùng một loại dữ liệu, một hằng số ngày giờ của cùng một loại dữ liệu hoặc với một biểu diễn chuỗi của một giá trị của loại dữ liệu đó. Ngoài ra, giá trị DẤU THỜI GIAN KHÔNG CÓ Múi giờ có thể được so sánh với giá trị DẤU THỜI GIAN CÓ Múi giờ

Tất cả các so sánh đều theo trình tự thời gian, có nghĩa là thời điểm càng xa từ ngày 1 tháng 1 năm 0001 thì giá trị của thời điểm đó càng lớn. Thời gian 24. 00. 00 so sánh lớn hơn thời gian 00. 00. 00

So sánh liên quan đến giá trị THỜI GIAN và biểu diễn chuỗi giá trị thời gian luôn bao gồm giây. Nếu biểu diễn chuỗi bỏ qua giây, thì không có giây nào được ngụ ý

Các so sánh liên quan đến giá trị dấu thời gian được đánh giá theo các quy tắc sau

Khi so sánh các giá trị dấu thời gian với các độ chính xác khác nhau, độ chính xác cao hơn được sử dụng để so sánh và mọi chữ số bị thiếu trong giây phân số được coi là bằng không
Khi so sánh giá trị DẤU THỜI GIAN CÓ Múi giờ với giá trị DẤU THỜI GIAN KHÔNG CÓ Múi giờ, giá trị DẤU THỜI GIAN KHÔNG CÓ Múi giờ được chuyển thành DẤU THỜI GIAN CÓ Múi giờ trước khi so sánh được thực hiện
Khi so sánh hai giá trị DẤU THỜI GIAN VỚI Múi giờ, việc so sánh được thực hiện bằng cách sử dụng biểu diễn UTC của các giá trị. Hai giá trị DẤU THỜI GIAN VỚI Múi giờ được coi là bằng nhau nếu chúng đại diện cho cùng một phiên bản trong UTC, bất kể độ lệch múi giờ được lưu trữ trong các giá trị. Ví dụ: '1999-04-15-08. 00. 00-08. 00' (8. 00 một. m. Giờ chuẩn Thái Bình Dương) giống như '1999-04-15-11. 00. 00-05. 00' (11. 00 một. m. Giờ chuẩn phương Đông)
Khi so sánh giá trị dấu thời gian với biểu diễn chuỗi của dấu thời gian, biểu diễn chuỗi trước tiên được chuyển thành kiểu dữ liệu của toán hạng dấu thời gian. Ngoại trừ giá trị được chuyển đổi có độ chính xác là 12. Nếu toán hạng dấu thời gian là DẤU THỜI GIAN KHÔNG CÓ Múi giờ, thì chuỗi không được chứa thông số múi giờ
So sánh dấu thời gian là theo trình tự thời gian mà không tính đến các biểu diễn có thể được coi là tương đương. Ví dụ, vị từ sau đây là đúng.
```
TIMESTAMP('1990-02-23-00.00.00') > '1990-02-22-24.00.00'
```

ví dụ 1. Bảng TABLE1 có 2 cột. C1, được định nghĩa là DẤU THỜI GIAN VỚI Múi giờ; .

CREATE TABLE TABLE1 (C1 TIMESTAMP WITH TIME ZONE, C2 TIMESTAMP);

Một hàng được chèn vào bảng với câu lệnh INSERT sau. Các giá trị đầu vào được cung cấp bởi các biểu diễn chuỗi ký tự của dấu thời gian với múi giờ.

INSERT INTO TABLE1 VALUES ( '2007-11-05-08.00.00-08:00', '2007-11-05-08.00.00');

Giả sử rằng múi giờ ngầm định là -5:00, câu lệnh SELECT sau đây sẽ không trả về bất kỳ hàng nào. Biểu diễn chuỗi của giá trị DẤU THỜI GIAN KHÔNG CÓ Múi giờ được chuyển thành giá trị DẤU THỜI GIAN CÓ Múi giờ, dẫn đến dấu thời gian có giá trị múi giờ là '2007-11-05-08.00.00-05:00' cho cột C2. Vị từ so sánh là sai vì hai giá trị không bằng nhau.

SELECT 1 FROM TABLE1 WHERE C1 = C2;

ví dụ 2. Khi giá trị DẤU THỜI GIAN KHÔNG CÓ Múi giờ được so sánh với một biểu diễn chuỗi của DẤU THỜI GIAN KHÔNG CÓ Múi giờ hoặc giá trị DẤU THỜI GIAN CÓ Múi giờ, thì biểu diễn chuỗi được chuyển thành DẤU THỜI GIAN KHÔNG CÓ Múi giờ (bất kể chuỗi có chứa múi giờ hay không). Việc so sánh được thực hiện bằng cách sử dụng hai giá trị DẤU THỜI GIAN KHÔNG CÓ Múi giờ. Giả sử rằng string_hv chứa dấu thời gian có giá trị múi giờ là '2007-11-05-08.00.00-08:00'. Giá trị chuỗi được chuyển thành giá trị DẤU THỜI GIAN KHÔNG CÓ Múi giờ của '2007-11-05-08.00.00', giá trị này được so sánh với giá trị được lưu trữ trong cột C2. Câu lệnh SELECT sau đây trả về một hàng đơn vì một hàng tồn tại trong bảng có dấu thời gian không có giá trị múi giờ là '2007-11-05-08.00.00'.

SELECT 1 FROM TABLE1 WHERE C2 = :string_hv;

Thật khó để sử dụng ngày tháng trong truy vấn máy chủ SQL, đặc biệt nếu bạn không có kiến thức tốt về cách thức hoạt động của loại DateTime trong máy chủ SQL. Ví dụ: một trong những truy vấn SQL thường được hỏi trong cuộc phỏng vấn là "chọn tất cả các hàng có ngày là 20151007?"

chọn * từ bảng trong đó ngày = '20151007'

Có thể có hoặc không, hoàn toàn phụ thuộc vào dữ liệu trong bảng của bạn. Khi bạn chỉ cung cấp phần ngày của biến DateTime, nó sẽ sử dụng '00. 00. 00. 000' cho phần thời gian.

Vì vậy, nếu bạn có bất kỳ hàng nào có cùng ngày nhưng khác giờ thì truy vấn này sẽ không hoạt động. Ví dụ: bạn có bảng Đơn hàng nơi bạn có hai đơn hàng, một đơn hàng có order_date='20150107' và đơn hàng khác có order_date='20150107:01:00:00:000' , thì truy vấn trên sẽ chỉ trả về đơn đặt hàng đầu tiên. Tôi sẽ giải thích cách tìm các hàng giữa các ngày trong SQL Server chi tiết hơn trong bài viết này.

Nhiều lập trình viên Java hoặc C++ sử dụng SQL Server không chú ý đầy đủ đến kiểu dữ liệu của cột ngày tháng e. g. order_date, trade_date, v.v. và nhiều người trong số họ thậm chí không biết rằng SQL Server sẽ so sánh tình trạng của họ với dữ liệu trong cột đó như thế nào. Không có gì ngạc nhiên vì đối với họ SQL là kỹ năng phụ, trong khi C++ hoặc Java là kỹ năng chính, nhưng thành thật mà nói sẽ có rất ít ứng dụng trong thế giới thực mà bạn không phải làm việc trên SQL

Vì vậy, nếu bạn muốn nâng cao kiến thức của mình về các nguyên tắc cơ bản của SQL, đặc biệt là trong SQL Server, tôi khuyên bạn nên tham gia Microsoft SQL dành cho người mới bắt đầu để tìm hiểu thêm về các loại dữ liệu NGÀY, GIỜ và DATETIME. Tôi đã tham khảo cuốn sách đó trước khi viết bài này và nó đã giúp tôi rất nhiều trong việc lấp đầy lỗ hổng kiến thức của mình

Truy vấn so sánh ngày trong SQL

Để hiểu cách so sánh ngày tháng trong SQL, hãy xem một ví dụ trong đó trường DateTime cũng có một số giá trị thời gian

Giả sử chúng ta có bảng sau với varchar course_name và datetime course_date columns:

IF OBJECT_ID( 'tempdb..#Course' ) IS NOT NULL
DROP TABLE #Course;

CREATE TABLE #Course (course_name varchar(10), course_date datetime);

INSERT INTO #Course VALUES ('Java', '2015-10-06 11:16:10.496');
INSERT INTO #Course VALUES ('MySQL', '2015-10-07 00:00:00.000');
INSERT INTO #Course VALUES ('SQL SERVER', '2015-10-07 11:26:10.193' );
INSERT INTO #Course VALUES ('PostgreSQL', '2015-10-07 12:36:10.393');
INSERT INTO #Course VALUES ('Oracle', '2015-10-08 00:00:00.000');

Bây giờ, bạn cần viết một truy vấn để nhận tất cả các khóa học vào ngày '2015-10-07'?

________số 8_______
Hãy xem giải pháp khác hoạt động như thế nào

Nếu bạn chỉ so sánh ngày với toán tử = và chỉ cung cấp ngày, bạn sẽ nhận được các hàng có trường thời gian bằng 0 vì máy chủ SQL sẽ sử dụng '00. 00. 00. 000" cho thời gian, như trong ví dụ sau.

SELECT * FROM #Course WHERE course_date = '2015-10-07'
course_name course_date
MySQL 2015-10-07 00:00:00.000

Bạn có thể thấy nó chỉ mang lại một hàng, trong đó thời gian bằng 0, nó không khớp với hai hàng khác khi thời gian khác không. Xem Truy vấn Microsoft SQL Server 2012 để tìm hiểu thêm về cách máy chủ SQL xử lý các định dạng ngày và thông tin ngày và giờ bị thiếu

Giải pháp 2 - So sánh ngày với mệnh đề between

Có vẻ như giữa là tùy chọn phù hợp để so sánh các ngày không tính thời gian. Bạn có thể đặt ngày hiện tại và ngày tiếp theo để bao gồm tất cả các thời điểm có ngày giống nhau, nhưng tiếc là điều đó sẽ không hoạt động. Nó cũng sẽ lấy giá trị ngày tiếp theo như bên dưới.

SELECT * FROM #Course WHERE course_date between '2015-10-07' and '2015-10-08'
course_name course_date
MySQL       2015-10-07 00:00:00.000
SQL SERVER  2015-10-07 11:26:10.193
PostgreSQL  2015-10-07 12:36:10.393
Oracle      2015-10-08 00:00:00.000

Bạn có thể thấy giữa cũng được tìm nạp 2015-10-08 00. 00. 00. 000 giá trị, không mong muốn. Điều này xảy ra vì mệnh đề BETWEEN sẽ luôn kéo bất kỳ giá trị nào của ngày tiếp theo nửa đêm. Xem Truy vấn Microsoft SQL Server 2012 để tìm hiểu thêm về chủ đề này. Cuộc thảo luận cũng hợp lệ cho SQL Server 2014.

Luôn sử dụng >= và < để so sánh ngày tháng

Câu trả lời đúng là sử dụng các toán tử lớn hơn (>) và nhỏ hơn (<). Điều này sẽ làm việc như mong đợi. Để sử dụng tùy chọn này, chỉ cần đặt ngày và ngày tiếp theo vào mệnh đề where như hình bên dưới

SELECT * FROM #Course WHERE course_date >= '2015-10-07' and course_date < '2015-10-08'
course_name course_date
MySQL       2015-10-07 00:00:00.000
SQL SERVER  2015-10-07 11:26:10.193
PostgreSQL  2015-10-07 12:36:10.393

Bạn có thể thấy chúng tôi có chính xác ba 3 hàng như mong đợi. Chỉ cần nhớ sử dụng < ở vị từ thứ hai, điều này sẽ đảm bảo rằng '2015-10-08 00. 00. 00. 000' sẽ không được chọn.

Đó là tất cả về cách so sánh các cột ngày tháng trong SQL Server. Bạn có thể thấy rằng truy vấn SQL sai ngày rất dễ xảy ra. Đôi khi bạn cảm thấy truy vấn của mình hoạt động tốt nhưng lại không thành công trong môi trường thực, Tại sao? . Trong QA nếu bạn không có bất kỳ giá trị ngày nửa đêm nào thì mệnh đề ngày giữa sẽ hoạt động tốt nhưng nếu bạn có giá trị nửa đêm trong môi trường thực thì nó sẽ không thành công

Cách đúng để so sánh các giá trị chỉ ngày với cột DateTime là sử dụng điều kiện '00. 00. 00. 000' và kết thúc tại "59. 59. 59. 999" .

Cũng đáng ghi nhớ là khi bạn thực hiện date = '2015-10-08' và nếu ngày là cột DateTime thì SQL Server . 00. 00. giá trị 000'. Vì vậy, xin vui lòng nhận thức được điều đó. Nhân tiện, nếu bạn muốn thực hành các truy vấn SQL đầy thử thách thì bạn cũng có thể xem Joe Celko's SQL Puzzles and Answers, Second Edition, một trong những cuốn sách thú vị để cải thiện kỹ năng SQL.

Các bài viết SQL Server khác mà bạn có thể thích

Làm cách nào để tham gia ba bảng trong một Truy vấn SQL?
Làm cách nào để xóa khỏi bảng bằng phép nối trong SQL?
Làm cách nào để thay thế null bằng Chuỗi trống trong SQL Server?
Sự khác biệt giữa mệnh đề WHERE và HAVING trong SQL?
Làm cách nào để thêm các cột trên bảng hiện có trong Microsoft SQL Server?
Sự khác nhau giữa row_number(), rank(), vàdens_rank() trong SQL?
Làm cách nào để tăng độ dài của cột varchar hiện có trong SQL Server?
Làm cách nào để chỉ lấy ngày hoặc giờ từ hàm GETDATE() trong SQL Server?
Sự khác biệt giữa các truy vấn SQL trong Oracle và Microsoft SQL Server?
Làm cách nào để tìm độ dài của Chuỗi trong MSSQL?
Truy vấn SQL để tìm tất cả tên bảng trong cơ sở dữ liệu?

Học thêm
Giới thiệu về SQL
Chương trình đào tạo SQL hoàn chỉnh
SQL cho người mới. Phân tích dữ liệu cho người mới bắt đầu

Làm cách nào để so sánh hai ngày trong mệnh đề WHERE trong SQL?

Bây giờ, chúng ta có thể so sánh ngày theo hai cách. .

Sử dụng toán tử so sánh

Sử dụng mệnh đề BETWEEN

Sử dụng toán tử so sánh với TimeStamp

Truyền DATETIME thành NGÀY mà không có Dấu thời gian

Sử dụng Mệnh đề Giữa với DATETIME

Làm cách nào để so sánh hai dấu thời gian trong MySQL?

Để tính toán sự khác biệt giữa các dấu thời gian trong MySQL, hãy sử dụng hàm TIMESTAMPDIFF(đơn vị, bắt đầu, kết thúc) . Đối số đơn vị có thể là MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER hoặc Year. Để có sự khác biệt tính bằng giây như chúng ta đã làm ở đây, hãy chọn SECOND.

Làm cách nào để sử dụng mệnh đề WHERE cho ngày trong MySQL?

Bạn nên sử dụng định dạng này trong biểu thức CẬP NHẬT và trong mệnh đề WHERE của câu lệnh CHỌN. Ví dụ. SELECT * FROM t1 WHERE date >= '2003-05-05'; Để thuận tiện, MySQL tự động chuyển đổi ngày thành số nếu ngày đó là .

Làm cách nào để so sánh hai trường ngày trong MySQL?

Để đếm sự khác biệt giữa các ngày trong MySQL, hãy sử dụng hàm DATEDIFF(ngày kết thúc, ngày bắt đầu) . Sự khác biệt giữa ngày bắt đầu và ngày kết thúc được biểu thị bằng ngày.

programming mysql