Tìm hiểu về Power Query / M Language (Part 7): Kiểu dữ liệu – Thời gian

Thời gian – cái gì đó liên quan đến thời gian. M cung cấp một số kiểu thời gian: ngày, giờ, ngày giờ, múi giờ và khoảng thời gian.

Nhóm này có nhiều điểm chung, vì vậy chúng ta sẽ tìm hiểu nó khác một chút so với các kiểu chúng ta đã gặp trước đây. Đầu tiên, tôi sẽ giới thiệu từng loại và xem xét các khía cạnh của chúng. Sau đó, chúng ta sẽ kiểm tra xem các loại khác nhau trong gia đình này hoạt động cùng nhau như thế nào (như chức năng chung).

Chúng ta đôi khi sẽ đề cập đến các thành viên gia đình theo cách này hay cách khác trực tiếp giữa ngày và / hoặc giờ là ngày / giờ là anh chị em (đây là ngày, giờ, ngày giờ và múi giờ). Loại còn lại trong họ như thời lượng, không chứa ngày hoặc giờ, vì vậy chúng ta sẽ chỉ gọi nó bằng tên. Chúng ta có thể coi đó là một phần của đại gia đình chẳng hạn như anh em họ, không phải anh chị em ruột thịt.

Ngày, Giờ và Thời lượng (Dates, Times & Durations)

Ngày (Date)

Nhập ngày, với cú pháp #date

#date(2021, 08, 27) // year, month, day - August 27, 2021
#date(3000, 12, 4) // December 4, 3000

Các năm từ 1 đến 9999 được hỗ trợ. Mặc dù ngày tháng là một chặng đường dài trong tương lai, nhưng nó không thể sử dụng được đối với những ngày trước Công nguyên (TCN).

#date(-25, 6, 10) // không được phép - năm không được trước thế kỷ thứ nhất sau Công nguyên 

Giờ (Time)

Loại thời gian được sử dụng cho các giá trị thời gian. Các phần của giây được hỗ trợ, giảm xuống mức chính xác 100 nano giây.

Nhập thời gian với cú pháp #time

#time(11, 15, 25) // hh, mm, ss — 11:15:25 AM
#time(13, 0, 0) // 1:00:00 PM
#time(13, 0, 0.53257) // 1:00:00.5325700 PM

Hãy lưu ý rằng giá trị thời gian khác với giá trị thời lượng (mà chúng ta sẽ nói ngay sau bên dưới trong bài). Nhập thời gian đại diện cho một thời điểm trong thời gian, một giá trị có thể được hiển thị trên mặt đồng hồ 24 giờ. thời lượng đại diện cho một lượng thời gian. Thời gian 1:00 sáng đại diện cho hay chính xác là 1:00 sáng. Ngược lại, thời lượng 1:00 thể hiện thực tế là một giờ đã trôi qua kể từ khi bắt đầu nhưng không cho biết thời điểm bắt đầu.

Khoảng thời gian được xác định từ nửa đêm đến nửa đêm 24 giờ sau.

#time(0, 0, 0) // 12:00:00 SA (khoảng thời gian nửa đêm)
#time(24, 0, 0) // 12:00:00 SA (nửa đêm, 24 giờ sau khi bắt đầu) 

00:00 và 24:00 có riêng biệt?

Cả 00:00 và 24:00 đều đề cập đến nửa đêm. Cả hai đều đề cập đến chính xác cùng một điểm trên mặt đồng hồ. Vì vậy, theo một nghĩa nào đó, chúng là một và giống nhau.

Tuy nhiên, từ góc độ con người, đôi khi chúng ta muốn phân biệt giữa hai điều này. Đôi khi chúng ta sử dụng 24:00 để chỉ nửa đêm khi theo quan điểm của tôi, nó kết thúc một ngày và 00:00 để chỉ nửa đêm khi nó bắt đầu một ngày. Giả sử, bạn muốn mô tả thực tế rằng bạn đã làm việc từ 10 giờ tối đến nửa đêm bằng cách sử dụng đồng hồ 24 giờ. Bạn có thể nói rằng bạn đã làm việc từ 22:00 đến 24:00. Mặt khác, nếu bạn muốn nói rằng bạn đã làm việc từ nửa đêm đến 2 giờ sáng, bạn có thể nói 00:00 đến 02:00. Theo nghĩa này, thời gian 00:00 và 24:00 là khác nhau.

Vì vậy, 00:00 và 24:00 vừa giống nhau vừa khác nhau. Nghịch lý này cách M xử lý hai trường hợp như thế nào?.

Trong M, cả #time(0, 0, 0) và #time(24, 0, 0) đều chỉ điểm trên mặt đồng hồ nơi giờ = 0, phút = 0 và giây = 0.

Time.ToRecord(#time( 0, 0, 0)) // [Hour = 0, Minute = 0, Second = 0]
Time.ToRecord(#time(24, 0, 0)) // [Hour = 0, Minute = 0, Second = 0]

Tuy nhiên, hai giá trị không bằng nhau và khi được chuyển đổi thành số, trả về các giá trị khác nhau:

#time(0, 0, 0) = #time(0, 0, 0) // true

#time(0, 0, 0) = #time(24, 0, 0) // false

#time(24, 0, 0) = #time(24, 0, 0) // true

Number.From(#time(0,0,0))  // 0
Number.From(#time(24,0,0)) // 1

Trong M, nếu bạn so sánh các giá trị thời gian của 00:00 và 24:00, bạn sẽ cần quyết định xem chúng là tương đương hay khác biệt và sau đó sử dụng logic để thực hiện so sánh theo cách bạn đã quyết định. Nếu cả hai phải khác nhau, toán tử ( = ) sẽ giải quyết giúp bạn tốt (như được minh họa trong ví dụ trên). Nếu cả hai nên được coi là tương đương, bạn sẽ cần sử dụng logic liên quan có thể là một cái gì đó như sau:

Time.ToRecord(#time(0, 0, 0)) = Time.ToRecord(#time(0, 0, 0)) // true
Time.ToRecord(#time(0, 0, 0)) = Time.ToRecord(#time(24, 0, 0)) // true
Time.ToRecord(#time(24, 0, 0)) = Time.ToRecord(#time(24, 0, 0)) // true

Ngày giờ ( DateTime )

Kết hợp các cú pháp về ngày và giờ với nhau và chúng ta có được loại ngày giờ.

#datetime(2021, 8, 27, 19, 30, 15) // yyyy, mm, dd, hh, mm, ss - August 27, 2021 7:30:15 PM

Phần thời gian của datetime khác về hành vi so với loại time ở một khía cạnh. time hỗ trợ thời gian đặc biệt là 24 giờ, 0 phút, 0 giây; ngày giờ thì không. Với datetime, để chỉ ra một thời điểm đúng 24 giờ sau khi bắt đầu một ngày, chỉ cần đặt datetime vào đầu ngày tiếp theo.

Múi giờ ( DateTimeZone )

datetimezone từ cú pháp ngày giờ và thêm múi giờ vào đó. Múi giờ được định nghĩa là sự chênh lệch của giờ và phút so với UTC, không phải là một cái tên thân thiện như “Indochina Time” hoặc “Western Indonesian Time”.

#datetimezone(2021, 8, 27, 19, 30 ,15, 07, 00) // yyyy, mm, dd, hh, mm, ss, hours +/- UTC, minutes +/- UTC - 8/27/2021 7:30:15 PM +07:00

Thời lượng ( Duration)

Thời lượng (duration) đại diện cho một lượng thời gian. Nó không có ngày hoặc giờ cụ thể. Thay vào đó, nó biểu thị lượng thời gian đã trôi qua hoặc lượng thời gian còn lại.

Ví dụ: bạn có thể sử dụng thời lượng (duration) để biểu thị thực tế là một lớp học dài 1 giờ 45 phút:

#duration(0, 1, 45, 0) // days, hours, minutes, seconds - 0d 1h 45m 0s

Giống như thời gian (time), thời lượng (duration) có thể lưu trữ các giá trị ở mức độ chính xác 100 nano giây.

#duration(0, 6, 50, 25.789) // 0d, 6h, 50m, 25.789s

Không giống như thời gian, thời lượng (duration) có thể tăng (số dương) hoặc giảm (số âm). Mặc dù thời gian (time) xử lý khoảng thời gian lên đến một ngày, nhưng thời lượng có thể chỉ kéo dài hơn 10,675,199 ngày theo hướng tăng hoặc giảm.

#duration(2, 6, 5, 10) // 2d, 6h, 5m, 10s
#duration(-250, -12, -11, -5.5) // -(250d, 12h, 11m, 5.5s)

Thời lượng (duration) linh hoạt trong việc chuyển đổi giữa các đơn vị đo lường. Ví dụ: thời lượng (duration) được khởi tạo là 240 phút tương đương với thời lượng được khởi tạo là 4 giờ vì thời lượng biết rằng 60 phút bằng 1 giờ.

#duration(0, 4, 0, 0) // 4h
#duration(0, 0, 240, 0) // kết quả giống với thời lượng trên vì nó đại diện cho cùng một lượng thời gian 

Khi khởi tạo, các giá trị đối số dương và âm thậm chí có thể được kết hợp để tạo ra thời lượng bằng tổng của chúng:

#duration(2, -24, 0, 0) // thời lượng 1 ngày ((2 ngày) + (-24 giờ) = 1 ngày)
#duration(0, 1, -5, 0) // thời lượng 55 phút ((1 giờ) + (-5 phút) = 55 phút) 

Điểm tương đồng

Bây giờ chúng ta đã tìm hiểu toàn bộ gia đình ngày / giờ và các anh chị em (ngày ‘#date‘, giờ ‘#time‘, ngày giờ ‘#datetime’ và múi giờ ‘#datetimezone‘) và anh em họ thời lượng ‘#duration‘ hãy xem xét các hành vi và đặc điểm chung giữa nhiều thành viên trong gia đình (Thời gian).

Sự kết hợp

Sử dụng toán tử kết hợp giữa ngày ‘#date‘ và giờ ‘#time‘ và kết quả là gì? tất nhiên sẽ là một ngày giờ ‘datetime‘!

#date(2021, 8, 27) & #time(19, 30, 10) //datetime: August 27, 2021 7:30:10 PM

Tiện dụng khi bạn muốn kết hợp cột ngày và cột thời gian thành cột ngày giờ.

Chuyển đổi

Nếu loại ngày / giờ hợp lý (đúng kiểu ngày / giờ), các loại ngày / giờ có thể được chuyển đổi thành các loại khác:

DateTime.From(#date(2021, 12, 31)) // datetime: December 31, 2021 12:00.00 AM

Việc chuyển đổi từ một loại có nhiều thông tin hơn (như ngày giờ) sang một loại chứa ít thông tin hơn (như ngày hoặc giờ) dẫn đến việc cắt bớt thông tin bổ sung.

Date.From(#datetime(2021, 10, 15, 13, 25, 12)) // date: October 15, 2021
Time.From(#datetime(2021, 10, 15, 13, 25, 12)) // time: 1:25:12 PM

Ví dụ trên hữu ích khi làm việc với cột bảng có chứa datetime (hoặc datetimezone) nơi bạn chỉ quan tâm đến phần ngày hoặc phần thời gian. Bạn chỉ cần chuyển đổi loại của cột thành ngày (hoặc giờ) và thông tin không liên quan sẽ bị loại bỏ.

Khi chuyển đổi ngày / ngày giờ sang múi giờ datetimezone, M giả định rằng giá trị đầu vào có liên quan đến múi giờ địa phương của hệ thống (máy tính).

DateTimeZone.From(#date(2021, 12, 31)) // December 31, 2021 12:00.00 AM với độ lệch múi giờ được đặt thành chênh lệch múi giờ hiện tại của hệ thống.

DateTimeZone.From(#datetime(2021, 10, 15, 13, 25, 12)) // October 15, 2021 1:25.12 PM theo múi giờ địa phương (local time zone)

Tương tự, việc chuyển đổi từ datetimezone sang một ngày / giờ khác sử dụng độ lệch múi giờ của giá trị bắt đầu và múi giờ địa phương của hệ thống để cho ra một giá trị liên quan đến múi giờ của hệ thống.

Ví dụ: trên hệ thống có chênh lệch múi giờ là -7 giờ:

DateTime.From(#datetimezone(2021, 8, 27, 0, 0, 0, 0, 0)) //- August 26, 2021 5:00:00 PM

Ở ví dụ trên, giá trị đầu ra sớm hơn đầu vào 7 giờ vì đầu ra có liên quan đến độ lệch múi giờ địa phương và khoảng thời gian đó sớm hơn độ lệch của đầu vào 7 giờ.

Toán học

Nếu nó có ý nghĩa (đúng kiểu ngày / giờ), các toán tử số học có thể được sử dụng với các giá trị thời gian.

Phép cộng
Thêm ngày / giờ và thời lượng và kết quả là một giá trị có cùng loại với loại ngày / giờ mà bạn đã bắt đầu, chỉ với thời lượng được thêm vào:

#date(2021, 8, 27) + #duration(1, 2, 0, 0) = August 28, 2021

#time(13, 5, 25) + #duration(0, 0, 0, 35) = 1:06:00 PM

#datetime(2021, 10, 25, 6, 13, 0) + #duration(0, -6, 0, 0) = October 25, 2021 12:13:00 AM

#datetimezone(2021, 10, 25, 6, 13, 0, 0, 0) + #duration(0, -4, 0, 0) = October 25, 2021 2:13:00 AM +00:00

Khi bạn thêm thời gian và khoảng thời gian, nó có thể hữu ích khi coi thời gian dưới dạng đồng hồ 24 giờ và thời lượng khi quay các kim trên đồng hồ đó về phía trước (hoặc phía sau) lượng thời gian được chỉ định bởi khoảng thời gian.

#time(22, 0, 0) + #duration(0, 4, 0, 0) = 2:00 AM

#time( 4, 0, 0) + #duration(2, 0, 0, 0) = 4:00 AM

Lưu ý trong ví dụ thứ hai ở trên, kết quả chính xác bằng giá trị của thời điểm ban đầu. Tại sao? Việc thêm 2 ngày vào thời điểm 4:00 sáng khiến kim đồng hồ của thời gian di chuyển về phía trước 24 giờ đối với ngày đầu tiên cộng thêm 24 giờ đối với ngày thứ hai. Sau tất cả chuyển động tịnh tiến đó, các kim đồng hồ sẽ dừng lại ở cùng một giờ, phút và giây nơi chúng bắt đầu: 4:00 sáng.

Vì phép cộng có tính chất giao hoán nên việc ngày / giờ hay thời lượng đứng trước trong phép công không quan trọng.

Phép trừ

Thời lượng cũng có thể được trừ cho ngày / giờ. Giá trị trả về sẽ cùng loại với ngày / giờ trong biểu thức. Lần này, thứ tự quan trọng: thời lượng có thể được trừ cho ngày / giờ, nhưng không phải ngược lại.

#time(13, 5, 25) - #duration(0, 0, 0, 25) = 1:05 pm
#duration(0, 0, 0, 25) - #time(13, 5, 25) // không được phép - ngày / giờ không thể bị trừ bởi thời lượng. 

Nếu, thay vì trừ một khoảng thời gian cho ngày / giờ, bạn trừ ngày / giờ cho một giá trị khác cùng loại, thì bạn sẽ nhận được gì? Tại sao, một khoảng thời gian mô tả sự khác biệt giữa hai giá trị!

#date(2021, 8, 27) - #date(2021, 8, 25) // duration: 2 days

#time(12, 0, 0) - #time(14, 0, 0) // duration: -2 hours

#datetimezone(2021, 10, 5, 16, 0, 0, 4, 0) - #datetimezone(2028, 10, 5, 15, 0, 0, -4, 0) // duration: -7 hours

Trong ví dụ thứ 3 ở trên, hãy để ý kết quả thời lượng được tính đúng như thế nào đối với chênh lệch múi giờ.

Một thời lượng ‘duration‘ có thể được thêm vào hoặc trừ đi từ một thời lượng ‘duration‘ khác.

#duration(1, 5, 0, 0) + #duration(0, 0, 25, 0) // 1d 5h 25m
#duration(1, 5, 0, 0) - #duration(0, 0, 25, 0) // 1d 4h 35m

Nhân & Chia

Không giống như anh chị em ngày / giờ, thời lượng có thể được nhân và chia cho các con số.

#duration(1, 5, 0, 0) * 3    // 3d 15h 0m
#duration(1, 5, 0, 0) / 2.5  // 11h 36m

Vì phép nhân có tính chất giao hoán nên thứ tự của các đối số không quan trọng: thời lượng ‘duration‘ rồi đến số và ngược lại đều được phép.

#duration(1, 5, 0, 0) * 3  = 3 * #duration(1, 5, 0, 0) // true

Điều tương tự không đúng đối với phép chia vì với phép thứ tự của các đối số là quan trọng.

Định dạng chuỗi

Việc chuyển đổi một giá trị thời gian thành văn bản sẽ tạo ra một chuỗi sử dụng định dạng mặc định.

Text.From(#date(2021, 12, 31)) // 12/31/2021

Text.From(#time(6, 12, 31)) // 6:12 AM - lưu ý: phần giây không được hiển thị

Text.From(#datetime(2010, 12, 31, 15, 16, 32)) // 12/31/2010 3:16:32 PM

Text.From(#datetimezone(2010, 12, 31, 15, 16, 32, -4, 15)) // 12/31/2010 3:16:32 PM -03:45

Text.From(#duration(15, 6, 3, 25.2)) // 15.06:03:25.2000000

Các định dạng mặc định được sử dụng có thể khác nhau tùy thuộc vào cài đặt hiện tại của hệ thống. Các kết xuất văn bản được hiển thị trong phần này giả định của en-US. Nếu hệ thống của bạn sử dụng một cài đặt khác, kết quả của bạn có thể khác nhau.

Đôi khi, các định dạng mặc định này không cắt được. Nếu bạn muốn kiểm soát nhiều hơn đối với kết quả đầu ra, mỗi loại thời gian cung cấp một phương thức ToText chấp nhận một chuỗi định dạng.

Giả sử bạn muốn hiển thị một ngày dưới dạng tên tháng dài theo sau là năm 4 chữ số (không có số ngày) hoặc có thể muốn tính thời gian bao gồm hai chữ số đầu tiên của thành phần giây (không giống như hiển thị mặc định được hiển thị trong ví dụ trên):

Date.ToText(#date(2021, 12, 31), "MMMM yyyy") // December 2021
Time.ToText(#time(12, 15, 18.253), "hh:mm:FF tt") // 12:15:25 PM

Tôi chưa thấy tài liệu chính thức xác định cú pháp cho các chuỗi định dạng. Nhưng chúng có vẻ rất với những gì được hỗ trợ bởi Microsoft .Net framework.

Một số chuỗi định dạng ngày / giờ hiển thị khác nhau tùy thuộc vào nền văn hóa (ví dụ: chuỗi định dạng cho tên tháng sẽ trả về giá trị bằng tiếng Anh cho nền văn hóa sử dụng tiếng Anh và bằng tiếng Nhật cho nền văn hóa sử dụng tiếng Nhật). Theo mặc định, các chuỗi định dạng được hiển thị liên quan đến văn hóa hiện tại của hệ thống cục bộ. Bạn có thể ghi đè điều này bằng cách chỉ định mã định danh văn hóa làm đối số thứ ba cho ToText.

Date.ToText(#date(2021, 12, 25), "m", "en-US") // December 25
Date.ToText(#date(2021, 12, 31), "m", "fr-FR") // 31 décembre
Date.ToText(#date(2021, 12, 31), "m", "ja-JP") // 12月31日
Date.ToText(#date(2021, 12, 25), "m", "vi-VN") // 25 Tháng Mười Hai

Lưu ý: Duration.ToText không chấp nhận đối số về văn hóa. Điều này có nghĩa là các tùy chọn định dạng của thời lượng là bất khả tri về văn hóa.

Một cách để đảm bảo rằng các giá trị được hiển thị nhất quán trên các hệ thống là chỉ ra văn hóa tham chiếu để sử dụng, như tôi đã làm ở trên. Một tùy chọn khác là sử dụng một chuỗi định dạng là bất khả tri văn hóa. Chuỗi định dạng “o” là một trong những chuỗi như vậy. Việc sử dụng nó dẫn đến chuỗi ngày giờ được xuất ra ở định dạng không đổi bất kể cấu hình văn hóa hiện tại của hệ thống là gì.

DateTime.ToText(#datetime(2021, 12, 25, 11, 50, 20), "o") // 2021-12-25T11:50:20.0000000

Kết luận

Hy vọng bạn đã nắm rõ về chức năng được hỗ trợ bởi họ (thời gian) này để bạn có thể sử dụng nó trong mã M. Ngoại trừ nghịch lý thời gian 00:00 so với 24:00.