Đây là cách chúng tôi làm điều đó.

1. Sử dụng dấu thời gian.

2. Khoảng thời gian sử dụng nửa mở để so sánh: start <= now < end.

Bỏ qua những người phàn nàn rằng BETWEEN bằng cách nào đó là điều cần thiết để SQL thành công.

Với một loạt các phạm vi ngày này thực sự dễ dàng để kiểm tra. Giá trị cơ sở dữ liệu cho 9/30 to 10/1bao gồm một ngày (30/9). Thời gian bắt đầu của khoảng tiếp theo phải bằng với kết thúc của khoảng trước. interval[n-1].end == interval[n].startQuy tắc đó rất hữu ích cho việc kiểm toán.

Khi bạn hiển thị, nếu bạn muốn, bạn có thể hiển thị định dạng startvà end-1. Hóa ra, bạn có thể giáo dục mọi người hiểu rằng "sự kết thúc" thực sự là ngày đầu tiên quy tắc không còn đúng nữa. Vì vậy, "9/30 đến 10/1" có nghĩa là "hợp lệ bắt đầu từ 9/30, không còn hợp lệ bắt đầu từ 10/1".

Oracle có kiểu dữ liệu TIMESTAMP . Nó lưu trữ năm, tháng và ngày của loại dữ liệu DATE, cộng với các giá trị giờ, phút, giây và phần giây.

Đây là một chủ đề trên asktom.oracle.com về số học ngày tháng.

Tôi thứ hai những gì S.Lott giải thích. Chúng tôi có một bộ sản phẩm sử dụng rộng rãi các phạm vi thời gian ngày và đó là một trong những bài học kinh nghiệm của chúng tôi để làm việc với các phạm vi như vậy. Nhân tiện, chúng tôi gọi ngày kết thúc dành riêng cho ngày kết thúc nếu nó không còn là một phần của phạm vi nữa (IOW, một khoảng thời gian nửa mở). Ngược lại, đó là ngày kết thúc bao gồm nếu nó được tính là một phần của phạm vi, điều này chỉ có ý nghĩa nếu không có phần thời gian.

Người dùng thường mong đợi đầu vào / đầu ra của phạm vi ngày bao gồm. Bằng mọi giá, hãy chuyển đổi đầu vào của người dùng càng sớm càng tốt sang phạm vi ngày kết thúc độc quyền và chuyển đổi bất kỳ phạm vi ngày nào càng muộn càng tốt khi nó phải được hiển thị cho người dùng.

Trên cơ sở dữ liệu, luôn lưu trữ các phạm vi ngày kết thúc độc quyền. Nếu có dữ liệu kế thừa với phạm vi ngày kết thúc bao gồm, hãy di chuyển chúng trên DB nếu có thể hoặc chuyển đổi sang phạm vi ngày kết thúc riêng càng sớm càng tốt khi dữ liệu được đọc.

Tôi sử dụng kiểu dữ liệu ngày tháng của Oracle và hướng dẫn các nhà phát triển về vấn đề các thành phần thời gian ảnh hưởng đến các điều kiện ranh giới.

Một ràng buộc cơ sở dữ liệu cũng sẽ ngăn chặn đặc tả ngẫu nhiên của một thành phần thời gian trong một cột không nên có và cũng cho trình tối ưu hóa biết rằng không có giá trị nào có thành phần thời gian.

Ví dụ, ràng buộc CHECK (MY_DATE = TRUNC (MY_DATE)) ngăn một giá trị có thời gian khác 00:00:00 được đặt vào cột my_date và cũng cho phép Oracle suy ra rằng một vị từ chẳng hạn như MY_DATE = TO_DATE (' 2008-09-12 15:00:00 ') sẽ không bao giờ đúng và do đó sẽ không có hàng nào được trả lại từ bảng vì nó có thể được mở rộng thành:

MY_DATE = TO_DATE('2008-09-12 15:00:00') AND
TO_DATE('2008-09-12 15:00:00') = TRUNC(TO_DATE('2008-09-12 15:00:00'))

Tất nhiên, điều này tự động là sai.

Mặc dù đôi khi việc lưu trữ ngày tháng dưới dạng số chẳng hạn như 20080915, điều này có thể gây ra các vấn đề tối ưu hóa truy vấn. Ví dụ, có bao nhiêu giá trị pháp lý trong khoảng từ 20.071.231 đến 20.070.101? Làm thế nào về giữa các ngày 31 tháng 12 năm 2007 và 01 tháng 1 năm 2008? Nó cũng cho phép nhập các giá trị bất hợp pháp, chẳng hạn như 20070100.

Vì vậy, nếu bạn có ngày không có thành phần thời gian thì việc xác định phạm vi trở nên dễ dàng:

select ...
from   ...
where  my_date Between date '2008-01-01' and date '2008-01-05'

Khi có một thành phần thời gian, bạn có thể thực hiện một trong những thao tác sau:

select ...
from   ...
where  my_date >= date '2008-01-01' and
       my_date  < date '2008-01-06'

hoặc là

select ...
from   ...
where  my_date Between date '2008-01-01'
                   and date '2008-01-05'-(1/24/60/60)

Lưu ý việc sử dụng (1/24/60/60) thay vì một số ma thuật. Việc thực hiện số học ngày bằng cách cộng các phân số được xác định trong ngày là khá phổ biến trong Oracle ... 3/24 trong ba giờ, 27/24/60 trong 27 phút. Phép toán Oracle thuộc loại này là chính xác và không bị lỗi làm tròn, vì vậy:

select 27/24/60 from dual;

... cho 0,01875, không phải 0,01874999999999 hoặc bất cứ điều gì.

Tôi chưa thấy các loại dữ liệu Khoảng thời gian được đăng.

Oracle cũng có các kiểu dữ liệu cho kịch bản chính xác của bạn. Có các kiểu dữ liệu INTERVAL DAY TO MONTH và INTERVAL DAY TO SECOND trong Oracle.

Từ tài liệu 10gR2.

INTERVAL YEAR TO MONTH lưu trữ một khoảng thời gian bằng cách sử dụng các trường ngày giờ YEAR và MONTH. Kiểu dữ liệu này hữu ích để biểu thị sự khác biệt giữa hai giá trị ngày giờ khi chỉ giá trị năm và tháng là quan trọng.

INTERVAL YEAR [(year_pre khít)] ĐẾN THÁNG

trong đó year_pre precision là số chữ số trong trường ngày giờ YEAR. Giá trị mặc định của year_pre precision là 2.

INTERVAL NGÀY ĐẾN Loại dữ liệu THỨ HAI

INTERVAL DAY TO SECOND lưu trữ một khoảng thời gian dưới dạng ngày, giờ, phút và giây. Kiểu dữ liệu này rất hữu ích để thể hiện sự khác biệt chính xác giữa hai giá trị ngày giờ.

Chỉ định loại dữ liệu này như sau:

INTERVAL DAY [(day_pre khít khao)] ĐẾN GIÂY [(fractional_seconds_preferences)]

Ở đâu

day_pre precision là số chữ số trong trường ngày giờ DAY. Các giá trị được chấp nhận là 0 đến 9. Giá trị mặc định là 2.

fractional_seconds_preferences là số chữ số trong phần phân số của trường ngày giờ thứ SECOND. Các giá trị được chấp nhận là 0 đến 9. Giá trị mặc định là 6.

Bạn có rất nhiều sự linh hoạt khi chỉ định các giá trị khoảng thời gian dưới dạng các ký tự. Vui lòng tham khảo "Chữ viết theo khoảng thời gian" để biết thông tin chi tiết về các giá trị khoảng thời gian được chỉ định dưới dạng chữ. Ngoài ra, hãy xem "Ví dụ về ngày giờ và khoảng thời gian" để biết ví dụ sử dụng khoảng thời gian.

Dựa trên kinh nghiệm của tôi, có bốn cách chính để làm điều đó:

1) Chuyển đổi ngày thành số nguyên kỷ nguyên (giây kể từ ngày 1 tháng 1 năm 1970) và lưu trữ nó trong cơ sở dữ liệu dưới dạng số nguyên.

2) Chuyển đổi ngày thành số nguyên YYYYMMDDHHMMSS và lưu trữ trong cơ sở dữ liệu dưới dạng số nguyên.

3) Lưu trữ nó như một ngày

4) Lưu trữ nó dưới dạng một chuỗi

Tôi luôn mắc kẹt với 1 và 2, vì nó cho phép bạn thực hiện số học nhanh chóng và đơn giản với ngày tháng và không dựa vào chức năng cơ sở dữ liệu cơ bản.

Dựa trên câu đầu tiên của bạn, bạn đang tình cờ phát hiện ra một trong những "tính năng" ẩn (tức là lỗi) của Java: java.util.Datelẽ ra là bất biến nhưng không phải vậy. (Java 7 hứa hẹn sẽ khắc phục điều này với một API ngày / giờ mới.) Hầu hết mọi ứng dụng doanh nghiệp đều dựa trên các mẫu thời gian khác nhau và tại một số thời điểm, bạn sẽ cần phải thực hiện số học vào ngày và giờ.

Lý tưởng nhất là bạn có thể sử dụng thời gian Joda , được sử dụng bởi Lịch Google. Nếu bạn không thể làm điều này, tôi đoán một API bao gồm một trình bao bọc xung quanh java.util.Datevới các phương thức tính toán tương tự như Grails / Rails và một phạm vi trình bao bọc của bạn (tức là một cặp có thứ tự cho biết bắt đầu và kết thúc một khoảng thời gian) sẽ đầy đủ.

Trong dự án hiện tại của tôi (một ứng dụng chấm công nhân sự), chúng tôi cố gắng chuẩn hóa tất cả các Ngày của chúng tôi thành cùng một múi giờ cho cả Oracle và Java. May mắn thay, các yêu cầu bản địa hóa của chúng tôi rất nhẹ (= 1 múi giờ là đủ). Khi một đối tượng liên tục không cần độ chính xác tốt hơn một ngày, chúng tôi sử dụng dấu thời gian là nửa đêm. Tôi sẽ đi xa hơn và nhấn mạnh vào việc loại bỏ thêm từng mili giây đến độ chi tiết thô nhất mà một đối tượng bền bỉ có thể chịu đựng được (nó sẽ giúp quá trình xử lý của bạn đơn giản hơn).

Tất cả các ngày có thể được lưu trữ rõ ràng dưới dạng dấu thời gian GMT (tức là không có múi giờ hoặc vấn đề đau đầu về tiết kiệm ánh sáng ban ngày) bằng cách lưu trữ kết quả của getTime () dưới dạng một số nguyên dài.

Trong trường hợp cần thao tác ngày, tuần, tháng, v.v. trong truy vấn cơ sở dữ liệu và khi hiệu suất truy vấn là tối quan trọng, thì dấu thời gian (được chuẩn hóa ở mức độ chi tiết cao hơn mili giây) có thể được liên kết với bảng phân tích ngày có các cột trong ngày , giá trị tuần, tháng, v.v. để các hàm ngày / giờ tốn kém không phải sử dụng trong các truy vấn.

Alan nói đúng- Thời gian của Joda thật tuyệt. java.util.Date và Calendar chỉ là một sự xấu hổ.

Nếu bạn cần dấu thời gian, hãy sử dụng kiểu ngày oracle với thời gian, hãy đặt tên cột bằng một số loại hậu tố như _tmst. Khi bạn đọc dữ liệu vào java, hãy đưa nó vào một đối tượng DateTime thời gian joda. để đảm bảo múi giờ chính xác, hãy xem xét rằng có các loại dữ liệu cụ thể trong oracle sẽ lưu trữ dấu thời gian với múi giờ. Hoặc bạn có thể tạo một cột khác trong bảng để lưu trữ ID múi giờ. Giá trị cho ID múi giờ phải là ID tên đầy đủ chuẩn cho Múi giờ, hãy xem http://java.sun.com/j2se/1.4.2/docs/api/java/util/TimeZone.html#getTimeZone%28java.lang.String% 29 . Nếu bạn sử dụng một cột khác cho TZ dta thì khi bạn đọc dữ liệu vào java, hãy sử dụng đối tượng DateTime nhưng đặt múi giờ trên đối tượng DateTime bằng cách sử dụng .withZoneRetainFields để đặt múi giờ.

Nếu bạn chỉ cần dữ liệu ngày (không có dấu thời gian) thì hãy sử dụng kiểu ngày trong cơ sở dữ liệu không có thời gian. một lần nữa đặt tên cho nó tốt. trong trường hợp này sử dụng đối tượng DateMidnight từ jodatime.

điểm mấu chốt: tận dụng hệ thống loại của cơ sở dữ liệu và ngôn ngữ bạn đang sử dụng. Tìm hiểu chúng và gặt hái những lợi ích của việc có api biểu cảm và cú pháp ngôn ngữ để giải quyết vấn đề của bạn.

CẬP NHẬT: Dự án Joda-Time hiện đang ở chế độ bảo trì. Nhóm của nó khuyên bạn nên chuyển sang các lớp java.time được tích hợp trong Java.

Joda-Time

Joda-Time cung cấp 3 lớp để thể hiện khoảng thời gian: Khoảng thời gian, Khoảng thời gian và Khoảng thời gian.

Tiêu chuẩn ISO 8601 quy định cách định dạng chuỗi biểu thị Khoảng thời gian và Khoảng thời gian . Joda-Time vừa phân tích cú pháp vừa tạo các chuỗi như vậy.

Múi giờ là một yếu tố quan trọng cần cân nhắc. Cơ sở dữ liệu của bạn phải lưu trữ các giá trị ngày-giờ của nó trong UTC. Nhưng logic kinh doanh của bạn có thể cần phải xem xét múi giờ. Thời gian bắt đầu của một "ngày" phụ thuộc vào múi giờ. Nhân tiện, hãy sử dụng tên múi giờ thích hợp thay vì mã 3 hoặc 4 chữ cái.

Các câu trả lời đúng bởi S. Lott một cách khôn ngoan khuyên để sử dụng nửa mở logic, như có hiệu quả nhất cho công việc ngày-thời gian. Khoảng thời gian bắt đầu là bao gồm trong khi phần kết thúc là độc quyền . Joda-Time sử dụng logic nửa mở trong các phương pháp của nó.

DateTimeZone timeZone_NewYork = DateTimeZone.forID( "America/New_York" );
DateTime start = new DateTime( 2014, 9, 29, 15, 16, 17, timeZone_NewYork );
DateTime stop = new DateTime( 2014, 9, 30, 1, 2, 3, timeZone_NewYork );

int daysBetween = Days.daysBetween( start, stop ).getDays();

Period period = new Period( start, stop );

Interval interval = new Interval( start, stop );
Interval intervalWholeDays = new Interval( start.withTimeAtStartOfDay(), stop.plusDays( 1 ).withTimeAtStartOfDay() );

DateTime lateNight29th = new DateTime( 2014, 9, 29, 23, 0, 0, timeZone_NewYork );
boolean containsLateNight29th = interval.contains( lateNight29th );

Đổ vào bảng điều khiển…

System.out.println( "start: " + start );
System.out.println( "stop: " + stop );
System.out.println( "daysBetween: " + daysBetween );
System.out.println( "period: " + period ); // Uses format: PnYnMnDTnHnMnS
System.out.println( "interval: " + interval );
System.out.println( "intervalWholeDays: " + intervalWholeDays );
System.out.println( "lateNight29th: " + lateNight29th );
System.out.println( "containsLateNight29th: " + containsLateNight29th );

Khi chạy…

start: 2014-09-29T15:16:17.000-04:00
stop: 2014-09-30T01:02:03.000-04:00
daysBetween: 0
period: PT9H45M46S
interval: 2014-09-29T15:16:17.000-04:00/2014-09-30T01:02:03.000-04:00
intervalWholeDays: 2014-09-29T00:00:00.000-04:00/2014-10-01T00:00:00.000-04:00
lateNight29th: 2014-09-29T23:00:00.000-04:00
containsLateNight29th: true

Tôi đang lưu trữ tất cả các ngày trong mili giây. Tôi hoàn toàn không sử dụng dấu thời gian / trường ngày giờ.

Vì vậy, tôi phải thao tác nó trong thời gian dài. Nó có nghĩa là tôi không sử dụng các từ khóa 'trước', 'sau', 'bây giờ' trong các truy vấn sql của mình.