2

I see everywhere that for get the minimum and the maximum of a date i have to use :

Date dateModificationMin = new Date( Long.MIN_VALUE );
Date dateModificationMax = new Date( Long.MAX_VALUE );

But i do it on my java.sql.date it's return : 055-12-02 for the min date 994-08-17 for the max date

How can i do for get the maximum and minimum of a java.sql.date?

Kvasir
  • 1,197
  • 4
  • 17
  • 31
  • 2
    Can you explain your use case? `java.sql.Date` is a bad implementation based on the `Date` class, and in any case, each DBMS has its own definition of what a `Date` means and what its range is. So why do you need these values? – RealSkeptic May 10 '16 at 12:10
  • It's for research some customer. The admin-user can choose the maximum and minimum date of creation of the user. But he don't have to do it. So in the case where he don't choose i have to put the minimum and maximum date in my sql request. – Kvasir May 10 '16 at 12:25
  • 1
    So why don't you build a different query for when the admin doesn't limit the date? – RealSkeptic May 10 '16 at 12:34
  • 1
    @Kvasir The minimum creation date of a user account would be when you first launched your system. The maximum would be the current moment. – Basil Bourque May 10 '16 at 16:50

2 Answers2

8

Database-specific

The answer is database-specific, not to be found in Java or JDBC or java.sql types. The limits on possible date-time values differs enormously between databases.

For example:

  • Postgres 9.5 TIMESTAMP WITH TIME ZONE has a range of 4713 BC to 294276 AD.
  • MySQL 5.7 TIMESTAMP type has a range of '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999' UTC. The range for DATETIME values is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999',
  • SQLite has no data types as such, and stores date-time values as ISO 8601 strings ("YYYY-MM-DD HH:MM:SS.SSS"). So presumably limited to year 9999 if it tracks only 4 digits for year (I do not know).
  • Firebird seems to have a TIMESTAMP from January 1, 1753, to December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds. I could not find their main reference page, nor update for version 3 just announced.
  • Microsoft SQL Server datetime2 type has a range of 0001-01-01 through 9999-12-31 through January 1,1 CE through December 31, 9999 CE, times from 00:00:00 through 23:59:59.9999999 (note the 7 digits of fraction, finer than microseconds but more coarse than nanoseconds). Also has datetime with range of January 1, 1753, through December 31, 9999, and time-of-day 00:00:00 through 23:59:59.997.

Instead of determining the absolute limit, I suggest you choose arbitrary points in the future and in the past, and use those as your limits. Far enough out to go beyond value to be encountered during the lifetime of your software and apps, but not so far out as to exceed the limits of most databases. Hard-code the limits as constants in your app, as enums in Java for example.

Other postings on Stack Overflow have shown that there is no clean simple answer to this question of finding date-time values to use as placeholders for unknown or yet-to-be-determined values (which I assume is the purpose behind the question).

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • I can't know if TIMESTAMP in MYSQL is limited to ```2038-01-19``` why it was found? – java dev Sep 27 '21 at 15:33
  • @devloper152 I do not understand your comment. – Basil Bourque Sep 27 '21 at 15:38
  • I mean in MySQL database timestamp range as you said MySQL 5.7 TIMESTAMP type has a range of '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999' so the end is 2038 so why we use it? – java dev Sep 27 '21 at 15:41
  • 1
    @devloper152 The advantage of `TIMESTAMP` in MySQL is that the values are grounded in the context of an offset-from-UTC. That offset is zero hours-minutes-seconds, commonly referred to as just being “UTC”. As for the limit of 2038, if that is not sufficient I suggest switching to a database system with a more robust set of date-time features, such as [Postgres](https://en.m.wikipedia.org/wiki/PostgreSQL). – Basil Bourque Sep 27 '21 at 15:48
  • @devloper152 If you are tracking moments, actual points on the timeline, you must store in a column of a type with a context of time zone or offset. Otherwise you would be storing a date and a time-of-day without knowing where, and therefore when. Search Stack Overflow to learn more, as this has been discussed many times already. – Basil Bourque Sep 27 '21 at 16:13
  • Thanx @Basil Bourque. But what does this sentence mean by The TIMESTAMP type is stored on disk in UTC timezone? – java dev Sep 27 '21 at 16:13
  • @devloper152 Read the documentation. And search Stack Overflow to learn more. – Basil Bourque Sep 27 '21 at 16:14
1

If you do the code to the java.util.Date, you will gain 

dateModificationMin = Sun Dec 02 17:47:04 CET 292269055
dateModificationMax = Sun Aug 17 08:12:55 CET 292278994

i guess, the date with negative long timestamp works wrong, so the minimum date is 

new Date(0)==  Thu Jan 01 01:00:00 CET 1970

Since java.sql.date extends the java.util.Date, the problem there is probably same

with 

        long timestamp = -10;

    for (int i = 0; i < 20; i++) {

        System.out.println("timestamp = " + timestamp + " date = " + (new Date(timestamp)));
        timestamp*=10;
    }

you get : 

timestamp = -10 date = Thu Jan 01 00:59:59 CET 1970
timestamp = -100 date = Thu Jan 01 00:59:59 CET 1970
timestamp = -1000 date = Thu Jan 01 00:59:59 CET 1970
timestamp = -10000 date = Thu Jan 01 00:59:50 CET 1970
timestamp = -100000 date = Thu Jan 01 00:58:20 CET 1970
timestamp = -1000000 date = Thu Jan 01 00:43:20 CET 1970
timestamp = -10000000 date = Wed Dec 31 22:13:20 CET 1969
timestamp = -100000000 date = Tue Dec 30 21:13:20 CET 1969
timestamp = -1000000000 date = Sat Dec 20 11:13:20 CET 1969
timestamp = -10000000000 date = Sun Sep 07 07:13:20 CET 1969
timestamp = -100000000000 date = Mon Oct 31 15:13:20 CET 1966
timestamp = -1000000000000 date = Sun Apr 24 23:13:20 CET 1938
timestamp = -10000000000000 date = Mon Feb 10 07:13:20 CET 1653
timestamp = -100000000000000 date = Thu Feb 26 15:13:20 CET 1200
timestamp = -1000000000000000 date = Tue Nov 16 23:13:20 CET 29720
timestamp = -10000000000000000 date = Sun Feb 01 07:13:20 CET 314912
timestamp = -100000000000000000 date = Mon Mar 08 15:13:20 CET 3166840
timestamp = -1000000000000000000 date = Sun Feb 24 23:13:20 CET 31686119

see how dates go crasy after -1 * 10^15 also with java.sql.Date :

timestamp = -10000000000000 date = 1653-02-10
timestamp = -100000000000000 date = 1200-02-26
timestamp = -1000000000000000 date = 720-11-16
timestamp = -10000000000000000 date = 912-02-01
timestamp = -100000000000000000 date = 840-03-08
timestamp = -1000000000000000000 date = 119-02-24
timestamp = 8446744073709551616 date = 634-03-09
timestamp = -7766279631452241920 date = 582-01-06

so wrong here !! :D

J J
  • 146
  • 2
  • 11