Chuyển đổi giữa các định dạng ngày và giờ của MySQL. Tạp chí cơ sở dữ liệu

Tất cả các hệ thống lớn hơn mà tôi đã giúp phát triển đều bắt buộc phải lưu trữ một số tổ hợp trường ngày và giờ. Hầu như phổ biến, các lĩnh vực này đều gặp khó khăn theo cách này hay cách khác. Một thách thức đặc biệt đáng nhớ là chuyển đổi giữa bộ nhớ trong và định dạng hiển thị có tên tháng thay vì số. Chúng tôi đã có thể khắc phục nó bằng cách sử dụng kết hợp các giải pháp lập trình và cơ sở dữ liệu, nhưng sẽ dễ dàng hơn nhiều nếu chúng tôi sử dụng MySQL. Khả năng xử lý ngày linh hoạt và toàn diện của nó cho phép bạn chuyển đổi giữa các định dạng ngày/giờ bên trong và bên ngoài một cách tương đối dễ dàng. Bây giờ tôi muốn cho bạn thấy những gì chúng ta có thể làm trong MySQL…

Một số khái niệm cơ bản về ngày/giờ

MySQL hỗ trợ nhiều kiểu kết hợp kiểu dữ liệu ngày/giờ. Tất cả những gì bạn phải làm, khi tạo cột ngày/giờ, là chọn một trong các loại sau phù hợp nhất với phần ngày/giờ và độ chính xác mà bạn yêu cầu

• NGÀY. Lưu trữ một giá trị ngày ở dạng YYYY-MM-DD. Ví dụ 2008-10-23
• THỜI GIAN. các giá trị trong 'HH. MM. định dạng SS' [hoặc 'HHH. MM. SS’ cho các giá trị giờ lớn]. Giá trị THỜI GIAN có thể nằm trong khoảng từ '-838. 59. 59' đến '838. 59. 59’
• NGÀY GIỜ. Lưu trữ giá trị ngày và giờ có dạng YYYY-MM-DD HH. MM. SS. Ví dụ 2008-10-23 10. 37. 22. Phạm vi ngày và giờ được hỗ trợ là 1000-01-01 00. 00. 00 cho đến 9999-12-31 23. 59. 59
• DẤU THỜI GIAN. Tương tự như DATETIME nhưng chính xác hơn, vì DẤU THỜI GIAN bao gồm mili giây. Cũng có một số khác biệt khác, tùy thuộc vào phiên bản MySQL và chế độ mà máy chủ đang chạy
• NĂM. Loại NĂM là loại một byte được sử dụng để biểu thị năm. Nó có thể được khai báo là NĂM[2] hoặc NĂM[4] để chỉ định chiều rộng hiển thị của hai hoặc bốn ký tự. Mặc định là bốn ký tự nếu không có chiều rộng được cung cấp. Đối với định dạng bốn chữ số, MySQL hiển thị các giá trị NĂM ở định dạng YYYY, với phạm vi từ 1901 đến 2155 hoặc 0000

Một bảng chứa các cột NGÀY và NGÀY được tạo theo cách tương tự như bất kỳ cột nào khác trong bảng. Ví dụ: chúng ta có thể tạo một bảng mới có tên là đơn hàng chứa số đơn hàng, mặt hàng đơn hàng, ngày đặt hàng và các cột giao hàng đơn hàng như sau

CREATE TABLE `MyDB`.`orders` [
  `order_no` INT  NOT NULL AUTO_INCREMENT,
  `order_item` TEXT  NOT NULL,
  `order_date` DATETIME  NOT NULL,
  `order_delivery` DATE  NOT NULL,
  PRIMARY KEY [`order_no`]
]

Mặc dù việc lưu trữ ngày tháng thông thường sử dụng dấu gạch ngang [-] làm dấu phân cách và dấu hai chấm [. ] làm dấu phân cách thời gian, trên thực tế có thể sử dụng bất kỳ ký tự nào, hoặc thậm chí không có ký tự nào, giữa phân đoạn ngày và giờ. Do đó, các định dạng sau đều biểu thị cùng ngày và giờ

2009-10-20 10:39:11
20091020103911
2009/10/20 10.39.11
2009*10*20*10*39*11

Giá trị đầu vào

MySQL sẽ vui vẻ chấp nhận bất kỳ câu lệnh nào sau đây làm đầu vào ngày

INSERT INTO tbl_name [idate] VALUES [19970505];
INSERT INTO tbl_name [idate] VALUES ['19970505'];
INSERT INTO tbl_name [idate] VALUES ['97-05-05'];
INSERT INTO tbl_name [idate] VALUES ['1997.05.05'];
INSERT INTO tbl_name [idate] VALUES ['1997 05 05'];
INSERT INTO tbl_name [idate] VALUES ['0000-00-00'];
INSERT INTO tbl_name [idate] VALUES [CURDATE[]];

Định dạng yyyy-mm-dd được sử dụng bởi MySQL được gọi là định dạng ngày Quốc tế [hoặc ISO]. Cái hay của việc sử dụng định dạng đó là nó giúp ngày và tháng không bị lẫn lộn. Tùy thuộc vào ngôn ngữ cơ sở dữ liệu, các tháng và ngày mơ hồ chẳng hạn như 3/4/1999 có thể được hoán đổi cho nhau. Tôi đã gặp vấn đề này nhiều lần trong Access, dựa trên định dạng ngày ngắn 'dd/mm/yyyy'

Các ứng dụng chèn ngày phải chuyển đổi chúng từ kiểu dữ liệu ngày thuần túy thành một chuỗi bao gồm một số biến thể của định dạng năm, tháng và ngày. Mã VBScript sau đây sẽ chuyển đổi ngày và giờ thành YYYY-M-D h. m. s chuỗi chẳng hạn như '2006-3-3 15. 2. 7’

dt = CDate[ dt ]
ISODateTime = "'" & Year[dt] & "-" & Month[dt] & "-" & Day[dt] _ 
	    & " " & Hour[dt] & ":" & Minute[dt] & ":" & Second[dt] & "'"

Lưu ý rằng, nếu không có định dạng thời gian trên dòng cuối cùng, phần thời gian sẽ bị bỏ qua và sẽ bị mất vĩnh viễn

Giá trị đầu ra

Theo mặc định, MySQL cũng hiển thị ngày ở định dạng Quốc tế

SELECT idate FROM tbl_name WHERE idate = CURDATE[];

--returns 2011-01-21

Để hiển thị ngày và giờ ở định dạng khác, hãy sử dụng hàm DATE_FORMAT[] linh hoạt của MySQL

DATE_FORMAT[date,format]

Nó chấp nhận một số chỉ định trong đối số định dạng. Đây là ký tự “%” theo sau là ký tự xác định định dạng. Ví dụ: %e chỉ định tháng dạng số;

dt = CDate[ dt ]
ISODateTime = "'" & Year[dt] & "-" & Month[dt] & "-" & Day[dt] _ 
	    & " " & Hour[dt] & ":" & Minute[dt] & ":" & Second[dt] & "'"

Làm việc với tên tháng

Tiêu chuẩn định dạng ngày nơi tôi làm việc là 'DD-MMM-YYYY' trong đó MMM là tên tháng gồm ba chữ cái viết tắt. Đã làm việc với định dạng này trong nhiều năm, tôi không thể nghĩ ra cách nào độc ác hơn để thúc đẩy các nhà phát triển. Đã có nhiều thử thách và đau khổ, nhưng giờ chúng ta đã hiểu rõ về nó. Đây là những gì tôi đã học được

Tốt nhất, bạn nên kiểm soát đầu vào bằng cách sử dụng một số loại tiện ích GUI giới hạn tên tháng thành ngày hợp lệ. Trình điều khiển thả xuống hoặc lịch hoạt động tốt cho mục đích này. Ngược lại, việc chấp nhận đầu vào ngày thông qua các trường văn bản dạng tự do đang gây rắc rối

Các thông tin đầu vào khác, chẳng hạn như thông tin được gửi từ đối tác qua dữ liệu XML, phải được chấp nhận ở bất kỳ định dạng nào được cung cấp cho bạn. Làm việc với các ngày chứa tên tháng đầy đủ có thể đặc biệt khó khăn. Trong những trường hợp này, hàm STR_TO_DATE[] của MySQL có thể trợ giúp. Nó là nghịch đảo của hàm DATE_FORMAT[]. Nó nhận một chuỗi giá trị [str] và một chuỗi định dạng [format]. Chuỗi định dạng có thể chứa các ký tự bằng chữ và các chỉ định định dạng bắt đầu bằng “%”. Các ký tự chữ trong định dạng phải khớp theo nghĩa đen trong str. Trình xác định định dạng ở định dạng phải khớp với phần ngày hoặc giờ trong str. STR_TO_DATE[] trả về giá trị ngày/giờ nếu chuỗi định dạng chứa cả phần ngày và giờ hoặc giá trị ngày hoặc giờ nếu chuỗi chỉ chứa phần ngày hoặc giờ. Nếu giá trị ngày/giờ trích xuất từ ​​stris không hợp lệ, STR_TO_DATE[] trả về NULL và đưa ra cảnh báo

dt = CDate[ dt ]
ISODateTime = "'" & Year[dt] & "-" & Month[dt] & "-" & Day[dt] _ 
	    & " " & Hour[dt] & ":" & Minute[dt] & ":" & Second[dt] & "'"

Hiển thị tên tháng khá dễ dàng vì hàm DATE_FORMAT[] có các chỉ định cho cả tên tháng đầy đủ và viết tắt [ %M và %b tương ứng ]. Hàm MONTHNAME[] cũng có thể được sử dụng ở đó. Chỉ cần cẩn thận với tên tháng viết tắt vì một số ngôn ngữ, như tiếng Pháp ['fr'], sử dụng chữ viết tắt bốn chữ cái. Vấn đề với chữ viết tắt tháng tiếng Pháp là ba chữ cái không đủ để phân biệt giữa tháng sáu và tháng bảy, đó là juin và juillet trong tiếng Pháp

Bắt đầu với MySQL 5. 0. 25, ngôn ngữ được chỉ định bởi biến hệ thống lc_time_names kiểm soát ngôn ngữ được sử dụng để hiển thị tên ngày, tháng và chữ viết tắt. Biến này ảnh hưởng đến đầu ra từ các hàm DATE_FORMAT[], DAYNAME[] và MONTHNAME[]. Tên địa phương có phần ngôn ngữ và khu vực được liệt kê bởi IANA [http. //www. iana. org/bài tập/ngôn ngữ-thẻ phụ-đăng ký] chẳng hạn như ‘ja_JP’ hoặc ‘pt_BR’. Giá trị mặc định là ‘en_US’

Hàm sau đọc phần ngôn ngữ của biến lc_time_namessystem để trả về tên tháng có ba chữ cái ở ngôn ngữ hiện tại. Nó chấp nhận số tháng làm tham số đầu vào, nằm trong khoảng từ 1 đến 12

CREATE FUNCTION `GetThreeLetterMonthName`[`month` SMALLINT] 
RETURNS char[3] 
CHARSET latin1 
LANGUAGE SQL 
NOT DETERMINISTIC 
CONTAINS SQL 
SQL SECURITY DEFINER 
COMMENT '' 
BEGIN  
    RETURN CASE WHEN LEFT[@@lc_time_names, 2] = 'fr' AND month = 7      
                THEN 'jul'      
                ELSE LEFT[MONTHNAME[STR_TO_DATE[month,'%c']], 3]    
           END;  
END;

Các bài kiểm tra sau nêu rõ cách giải quyết cho vấn đề tháng 6/tháng 7 bằng tiếng Pháp. Do đó, Juillet được hiển thị dưới dạng jul

dt = CDate[ dt ]
ISODateTime = "'" & Year[dt] & "-" & Month[dt] & "-" & Day[dt] _ 
	    & " " & Hour[dt] & ":" & Minute[dt] & ":" & Second[dt] & "'"

Sự kết luận

Khả năng xử lý ngày linh hoạt và triệt để của MySQL cho phép bạn chuyển đổi giữa các định dạng ngày/giờ bên trong và bên ngoài một cách tương đối dễ dàng. Ngoài ra, các tên tháng có ba chữ cái đầy thách thức theo truyền thống có thể được quản lý bằng cách sử dụng biến lc_time_namessystem của MySQL và các hàm DATE_FORMAT[], DAYNAME[] và MONTHNAME[] nhận biết ngôn ngữ

Làm cách nào để chuyển đổi ngày từ định dạng này sang định dạng khác trong MySQL?

Làm cách nào để thay đổi định dạng ngày từ YYYY

Làm cách nào để chuyển đổi ngày từ định dạng này sang định dạng khác trong SQL?

Ngày giờ và dấu thời gian có phải là cùng một loại dữ liệu trong MySQL không?