CODEWITHSUNDEEP
javasqldatetimestamp
kecy
kecy

Reputation: 23

subtracting two java.sql.timestamp give incorrect year

I am trying to get timestamp form sql and need to calculate the difference between two time stamp.

//startDate = 2014-07-10 16:07:00.0
//endDate = 2014-07-11 04:07:00.0
//END_DATE = 2014-07-18 08:07:00.0

private Timestamp calculateWflTime(String vehicleNum, Timestamp startDate,
        Timestamp endDate) {
    Timestamp t1 = new Timestamp (END_DATE.getTime()); //2014-07-18 08:07:00.0
    Timestamp t2 = new Timestamp (startDate.getTime()); //2014-07-10 16:07:00.0
        Timestamp wflTime = null;
        long diff=0;
        if(VEH_NUM == vehicleNum){
            diff = t1.getTime()-t2.getTime();//diff(END_DATE,startDate);
        }
    wflTime = new Timestamp( Math.abs(diff/(1000*60*60*24)));
    return wflTime; //1970-01-01 05:30:00.007
}

Upvotes: 0

Views: 1210

Answers (4)

Meno Hochschild
Meno Hochschild

Reputation: 44061

The expression

new Timestamp( Math.abs(diff/(1000*60*60*24)));

is semantically wrong from a domain point of view. Why? You try to convert an amount of time (indeed a duration in milliseconds not anchored on the timeline) to a time point which is fixed here to count from UNIX epoch (1970-01-01). It is like conversion of a length to a point in geometric terms.

The difference between two timestamps should not be a new timestamp but just a duration (here your diff-variable in milliseconds). And it is up to you how you want to normalize it to years and months.

UPDATE after answer of OP

Clean Joda solution:

public static void main(String... args) {
    Timestamp t1 = new Timestamp(0);
    Timestamp t2 = new Timestamp(86400000 + 7261000);
    System.out.println(getDurationJoda(t1, t2));
    // output: 1 day, 2 hours, 1 minute, 1 second.
}

public static String getDurationJoda(Timestamp start, Timestamp end) {
    LocalDateTime ldtStart = new LocalDateTime(start);
    LocalDateTime ldtEnd = new LocalDateTime(end);

    Period p = new Period(ldtStart, ldtEnd, PeriodType.dayTime());

    PeriodFormatter fmt =
        new PeriodFormatterBuilder()
        .appendDays().appendSuffix(" day, ", " days, ")
        .appendHours().appendSuffix(" hour, ", " hours, ")
        .appendMinutes().appendSuffix(" minute, ", " minutes, ")
        .appendSeconds().appendSuffix(" second.", " seconds.").toFormatter();
    return fmt.print(p);
}

Time4J-solution

Furthermore, you have this alternative using my library Time4J which contains a localizable PrettyTime-class for duration formatting since version 1.2:

private static final IsoUnit DAYS = CalendarUnit.DAYS;
private static final IsoUnit HOURS = ClockUnit.HOURS;
private static final IsoUnit MINUTES = ClockUnit.MINUTES;
private static final IsoUnit SECONDS = ClockUnit.SECONDS;

public static void main(String... args) {
  Timestamp t1 = new Timestamp(0);
  Timestamp t2 = new Timestamp(86400000 + 7261000);
  System.out.println(getDurationTime4J(t1, t2));
  // output: 1 day, 2 hours, 1 minute, and 1 second
}

public static String getDurationTime4J(Timestamp start, Timestamp end) {
  PlainTimestamp startTS = TemporalTypes.SQL_TIMESTAMP.transform(start);
  PlainTimestamp endTS = TemporalTypes.SQL_TIMESTAMP.transform(end);

  Duration<?> duration = 
    Duration.in(DAYS, HOURS, MINUTES, SECONDS).between(startTS, endTS);
  return PrettyTime.of(Locale.ENGLISH).print(duration, TextWidth.WIDE);
}

Last but not least, try to evaluate your string condition before formatting the duration and use equals() instead of ==, for example:

if (VEH_NUM.equals(vehicleNum)) {
 // call getDuration(..., ...)
} else {
 // return zero duration string
}

Upvotes: 1

kecy
kecy

Reputation: 23

anyways I used JodaTime library to calculate the difference between two time stamps. Thank you all for the help.

as a solution, the modified code is now :

private String calculateWflTime(String vehicleNum, Timestamp startDate,
        Timestamp endDate) {        
    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    String days = null, hours = null, minutes = null, seconds = null;
    Date d1 = null;
    Date d2 = null;
    Timestamp wflTime = null;
    if(VEH_NUM == vehicleNum){
        String dateStart = END_DATE.toString();
        String dateStop = startDate.toString();
    try {
        d1 = format.parse(dateStart);
        d2 = format.parse(dateStop);

        DateTime dt1 = new DateTime(d1);
        DateTime dt2 = new DateTime(d2);

         days = Days.daysBetween(dt1, dt2).getDays() + " days, ";
         hours = Hours.hoursBetween(dt1, dt2).getHours() % 24 + " hours, ";
         minutes = Minutes.minutesBetween(dt1, dt2).getMinutes() % 60 + " minutes, ";
         seconds = Seconds.secondsBetween(dt1, dt2).getSeconds() % 60 + " seconds.";

     } catch (Exception e) {
        e.printStackTrace();
     }          
    }
    return days+hours+minutes+seconds;
}

Upvotes: 0

latki8
latki8

Reputation: 1

Regarding the 1970 date, generally all dates are a number representing the number of milliseconds since midnight on 1-jan-1970, so if you display the difference between to similar dates as a date then you will get something in the 1970s. You need to treat the answer as milliseconds and work out the days,hours,minutes as a time. Hope this helps.

Upvotes: 0

Thomas
Thomas

Reputation: 699

Unix timestamp is a system for describing instants in time, defined as the number of seconds that have elapsed since 1 January 1970.

So if you substract it and transform it, it shows you the time elapsed since 1970.

Upvotes: 0

Related Questions