10 Replies Latest reply: Apr 1, 2013 3:40 PM by rp0428 RSS

    using 2400 as the end of the day

    user474583
      Hello,

      I've seen a number of posts around military time and whether or not 2400 is a valid time and suggestions on how to properly handle it, but I couldn't really find a post or direction that related directly to the situation I have. I have some users and they would like to use 2400 for time but are expecting that 2400 represents the end of the day and not the beginning of the day. When they see March 21, 24:00 they think of that as March 21, 23:59:59 and not March 21, 00:00:01. I can use the date format kkmm to parse 2400 as a valid time, so they can enter it and it will be saved to the database. The problem I'm having is that when it gets saved to the database 2400 still ends up being 0000 as the time, which is the beginning of the day. Is it possible to easily save 2400 for time in a date or timestamp field in the database and have it represent the end of day or would I need to do something like the following

      1) not allow 2400 and say that 2359 is what should be used to represent end of day
      2) or change to save the date as a varchar or number field and then have to change it to 2359 if I create a date object in the java code and want to do date arithmetic or comparisons.
        • 1. Re: using 2400 as the end of the day
          jtahlborn
          you realize that the end of one day and the beginning of the next day are actually the same time, right? so "March 21, 24:00" and "March 22, 00:00" are actually the same time...
          • 2. Re: using 2400 as the end of the day
            rp0428
            >
            The problem I'm having is that when it gets saved to the database 2400 still ends up being 0000 as the time, which is the beginning of the day. Is it possible to easily save 2400 for time in a date or timestamp field in the database and have it represent the end of day or would I need to do something like the following
            >
            What database are you talking about; Oracle?

            Regardless of the DB you are confusing date/time storage with date/time presentation. Physical storage format and 'logical' interpretation of the value are two DIFFERENT things.

            You cannot control how the data is physically stored. You can only control how you display it or interpret it.

            For example, Oracle physically stores the HOUR in excess-1 notation where the hour byte ranges from 1 to 24. It could have been stored using many other physical formats. That doesn't prevent you from displaying the hour in 12-hour format instead of 24 hour format.

            How you 'interpret' or display the value is up to you.

            See the physical internal formats discussed in the Oracle® Call Interface Programmer's Guide
            http://docs.oracle.com/cd/E11882_01/appdev.112/e10646/oci03typ.htm#i423684
            >
            DATE
            The DATE data type can update, insert, or retrieve a date value using the Oracle internal date binary format. A date in binary format contains 7 bytes, as shown in Table 3-4.

            Table 3-4 Format of the DATE Data Type
            Byte       1     2     3     4     5     6     7 
            Meaning  Century Year Month Day Hour Minute Second
             
            Example (for 30-NOV-1992, 3:17 PM)
                      119    192    11    30    16    18    1
            The century and year bytes (bytes 1 and 2) are in excess-100 notation. The first byte stores the value of the year, which is 1992, as an integer, divided by 100, giving 119 in excess-100 notation. The second byte stores year modulo 100, giving 192. Dates Before Common Era (BCE) are less than 100. The era begins on 01-JAN-4712 BCE, which is Julian day 1. For this date, the century byte is 53, and the year byte is 88. The hour, minute, and second bytes are in excess-1 notation. The hour byte ranges from 1 to 24, the minute and second bytes from 1 to 60. If no time was specified when the date was created, the time defaults to midnight (1, 1, 1).
            • 3. Re: using 2400 as the end of the day
              user474583
              I do realize that 2400 and 0000 are the same time and the issue is really an interpretation problem. The users do not see them as being the same time. They think of 0000 as being the start of the day and 2400 as being the end of the same day. So if they see two date and times like March 22, 0030 and March 22, 2400 they would be expecting that the the difference between those two date/times is 23.5 hours not a half hour.
              • 4. Re: using 2400 as the end of the day
                gimbal2
                ... I wish you luck trying to cater to the needs of misguided fools.
                • 5. Re: using 2400 as the end of the day
                  jtahlborn
                  user474583 wrote:
                  I do realize that 2400 and 0000 are the same time and the issue is really an interpretation problem. The users do not see them as being the same time. They think of 0000 as being the start of the day and 2400 as being the end of the same day. So if they see two date and times like March 22, 0030 and March 22, 2400 they would be expecting that the the difference between those two date/times is 23.5 hours not a half hour.
                  actually, i would say you are the one confused. the difference between those 2 times is 23.5 hours. why would you think that "March _22_, 00:00" == "March _22_, 24:00"? you'll notice in my example i stated that "March _21_, 24:00" == "March _22_, 00:00" (two different dates).
                  • 6. Re: using 2400 as the end of the day
                    user474583
                    maybe I am missing something or am confused, but when march 22, 24:00 gets saved to the database and you do just a select on that column the date and time is March 22 00:00:00 0 so to me that seems like it is the beginning of the day and if I calculate the duration between march 22, 00:00 and March 22, 00:30 to me it will be 30 minutes. I guess I could add a second to the time and then when the duration is calculated it would be 23.5 hours but I'm not really using the the date and time they entered.
                    • 7. Re: using 2400 as the end of the day
                      rp0428
                      >
                      when march 22, 24:00 gets saved to the database and you do just a select on that column the date and time is March 22 00:00:00 0
                      >
                      Post code that shows saving 'march 22, 24:00' to the database and the code that does 'a select on that column'.
                      • 8. Re: using 2400 as the end of the day
                        Skotty
                        I just had a thread recently that touched on a similar topic. In my case, the issue was in date parsing using the kk format. If using lenient parsing, 24 is seen the same as 0. It all comes down to how you represent that pesky first hour of the day.

                        Someone else posted a link to a standard where 2400 was indeed a special case that was supposed to represent end of day, though I doubt you will get any existing date code to work that way.

                        You will probably need a custom bit of code that looks for 2400 and deals with it accordingly. Whether you ultimately save it as the last instant of the day or the first instant of the next day or some special format that preserves the "midnight" concept will depend on your user requirements.

                        Edited by: Skotty on Mar 27, 2013 5:20 PM
                        • 9. Re: using 2400 as the end of the day
                          user474583
                          code at a higl level is following

                          1) we receive date as a string in the format ddmmyyyyhhmm - the hh can be 24
                          2) a java.util.date object is constructed from the string using Date aDate = new SimpleDateFormat("ddMMyyyykkmm").parse(theString);
                          3) this is moved to another Date object that is part of a domain object where the date is defined as

                          @Temporal(TemporalType.TIMESTAMP)
                          @Column(name = "SERVICE_END_DATE_TIME")
                          public Date getServiceEndDateTime() {
                               return serviceEndDateTime;
                          }

                          4) this is saved to database using EntityManaer.persist

                          when I say doing a select it is simply a select in an sql tool like sql developer.

                          Edited by: user474583 on Apr 1, 2013 11:19 AM
                          • 10. Re: using 2400 as the end of the day
                            rp0428
                            Read my reply again
                            >
                            Post code that shows saving 'march 22, 24:00' to the database and the code that does 'a select on that column'.
                            >
                            1. You did not post code
                            2. You did not show any 'march 22, 24:00'
                            3. You did not post anything saving any value to any database
                            4. You did not post code that does 'a select on that column'

                            There is NOTHING that is save 'march22, 24:00' to the database. Your Java code is parsing a string that includes 'kk' in the format and converting that value to a standard date/time and it is THAT which is being stored in the database.

                            When you ask a question don't tell us what you THINK is happening. Post the actual code so we can SEE what is happening.