Calculate time difference between two datetime in double digit hour,minutes and seconds format

Question

I am calculating time difference between two dates with format:

Hour:Minutes:Seconds

Right now i am getting output like this with the following input:

StartDate=2016-06-29 15:52:32.360

EndDate=2016-06-29 15:52:36.970

Output:  0 : 0 : 4

But i want to get double digit output in time :

Expected Output: 00 : 00 : 04

Input:

StartDate=2016-06-29 15:52:32.360

EndDate=2016-06-29 15:53:36.970

Expected output: 00 : 01 : 04

This is my code:

public class Attendance
{
      public int Id { get; set; }
      public Nullable<System.DateTime> StartDateTime { get; set; }
      public Nullable<System.DateTime> EndDateTime { get; set; }
}


var query = (from t in context.Attendance
             select new 
             {
             TotalTime =SqlFunctions.DateDiff("s",t.StartDateTime,t.EndDateTime) /3600 + " : "
            + SqlFunctions.DateDiff("s", t.StartDateTime, t.EndDateTime) % 3600 / 60
            + ": " + SqlFunctions.DateDiff("s", t.StartDateTime, t.EndDateTime) % 60,
              }).tolist();

Note:I dont want to do like below:

var query = (from t in context.Attendance.toList().
                  select new 
                  {
                     //code to calculate time difference and format time
                  }).tolist();

Subtracting one DateTime from another returns a TimeSpan. Just do the subtraction and formatting on the client — Panagiotis Kanavos, Jul 05 '16 at 13:13
@PanagiotisKanavos:is this what you are saying:t.EndDateTime.Value.Subtract(t.StartDateTime.Value)??? — I Love Stackoverflow, Jul 05 '16 at 13:15
Also note that EF is an ORM ie object-to-relational mapper, not a replacement for SQL. No objects or mapping are involved in this case. Whenever you see SQL concepts like joins or DateDiff leak into the ORM query, it's a strong indication that something is wrong — Panagiotis Kanavos, Jul 05 '16 at 13:16
You can use `-` between DateTime values as well. Of course, a get-only property that calculates the difference would be even better and reduce the clutter — Panagiotis Kanavos, Jul 05 '16 at 13:17
@Thomas :Can you please tell me how this string.format would work when this query will be converted in to equivalent sql query.Please before saying that this is duplicate question please read and understand question carefully — I Love Stackoverflow, Jul 05 '16 at 13:17
@Learning *don't* do this in the query. Add a simple property that returns the Timespan difference — Panagiotis Kanavos, Jul 05 '16 at 13:18
@Mikolaytis:Tostring will work when this query will be converted in to equivalent sql query?? — I Love Stackoverflow, Jul 05 '16 at 13:18
Hey, why not "simply" use `select new { TotalTime =(SqlFunctions.DateDiff("s",t.StartDateTime,t.EndDateTime) /3600).ToString("00") + " : " + (SqlFunctions.DateDiff("s", t.StartDateTime, t.EndDateTime) % 3600 / 60).ToString("00") + ": " + (SqlFunctions.DateDiff("s", t.StartDateTime, t.EndDateTime) % 60).ToString("00") }` and not actually "learning" about `TimeSpan` or `string` formatting... — Corak, Jul 05 '16 at 13:31
Sorry, I meant of course: `select new { TotalTime = ((SqlFunctions.DateDiff("s",t.StartDateTime,t.EndDateTime) /3600) ?? 0).ToString("00") + " : " + ((SqlFunctions.DateDiff("s", t.StartDateTime, t.EndDateTime) % 3600 / 60) ?? 0).ToString("00") + ": " + ((SqlFunctions.DateDiff("s", t.StartDateTime, t.EndDateTime) % 60) ?? 0).ToString("00") }` — Corak, Jul 05 '16 at 13:36

Panagiotis Kanavos · Answer 1 · 2016-07-05T13:45:10.560

1

First of all, EF deals with objects, it isn't a replacement for SQL queries. You can easily add a read-only property that calculates the difference between the two values, and returns whatever you want if either of them is null, eg:

public partial class Attendance
{
  public int Id { get; set; }
  public Nullable<System.DateTime> StartDateTime { get; set; }
  public Nullable<System.DateTime> EndDateTime { get; set; }

  public TimeSpan Duration 
  { 
       get 
       { 
           if (StartDateTime.HasValue && EndDateTime.HasValue)
           {
               return (EndDateTime.Value - StartDateTime.Value);
           }
           else {...}
       }
}

One you have the TimeSpan value you can format it using String.Format as the other answers show. In fact, formatting should be done as late as possible, preferably using a format string on the client or HTML view, to avoid mixing up client/server locales or having to parse the string back to a Timespan for farther processing

TimeSpan values can be formatted using either a standard or custom format string, eg:

  var label=attendee.Duration.ToString("c");

Will generate a string in the form [-][d'.']hh':'mm':'ss['.'fffffff]. If the duration doesn't contain a date or millisecond component, this will match the format you want.

Serializing this class to Json doesn't need any formatting at all, since Json.NET can serialize TimeSpan to the HH:mm:ss format, as shown in this example

edited Jul 05 '16 at 13:45

answered Jul 05 '16 at 13:22

Panagiotis Kanavos

120,703
13
188
236

This is my data model generated using Database first(.edmx) in which it is written that Manual changes to this file will be overwritten if the code is regenerated. – I Love Stackoverflow Jul 05 '16 at 13:23
No it won't, that's a basic feature of all generated classes in Visual Studio. The classes are *partial* so you can define other files with extra properties, methods etc that won't be overwritten. In fact, you'll notice that your class definition already includes the `partial` keyword – Panagiotis Kanavos Jul 05 '16 at 13:24
Ok i understand this so i should format this timespan object i.e Duration in my query with string.format?? – I Love Stackoverflow Jul 05 '16 at 13:30
How this .Tostring Function will work inside this linq query?? – I Love Stackoverflow Jul 05 '16 at 13:33
No, you should do that on the form, window or view where you want to use that value. Preferably using the format string attribute of most desktop or web controls. Otherwise you will find at least one case where you have to parse the string back into a TimeSpan for processing, comparison, ordering. – Panagiotis Kanavos Jul 05 '16 at 13:34
actually i am returning this as json object to ajax get call so i should do this formatting there?? – I Love Stackoverflow Jul 05 '16 at 13:35
You don't do that in the query. Formatting in the query is a bad design, whether you do this writing raw T-SQL or in a LINQ statement. Fromatting should be done on the *client* side. Whatever rendering technology you use (web form, WPF, report rendering) all support field formatting of timespans. None of them support reformating of strings that look like timestamps – Panagiotis Kanavos Jul 05 '16 at 13:36
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/116473/discussion-between-learning-and-panagiotis-kanavos). – I Love Stackoverflow Jul 05 '16 at 13:37
Json.NET already serializes TimeSpan values. You don't need to format the value – Panagiotis Kanavos Jul 05 '16 at 13:42
I am using angular js as client side so i was having a little hardtime calculating datetime difference and formatting as per my requirement that is why i am doing this in query but you a good point regarding user locales – I Love Stackoverflow Jul 05 '16 at 13:46

Calculate time difference between two datetime in double digit hour,minutes and seconds format

1 Answers1