Saving time as minutes into database

Question

I am using an API that analyzes a video and writes data into the database at every 5 minutes. The problem is, in the database the timing appears as seconds (300000, 600000, 900000...) instead of minutes. Is there a way for me to convert the timing into minutes before inserting it into the database?

getFrameTimeStamp = the particular minute that the video is at

//the code below means it will only write in every 5 minutes (300000)
if (has.getFrameTimeStamp() % 300000 == 0) {
    using (SqlConnection conn = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\sit\Desktop\(NEW) LEA WINFORMS\28June_LessonEffectivenessAnalysis\LessonEffectivenessAnalysis\LessonEffectivenessAnalysis\LessonEffectivenessAnalysis\LessonAnalysis.mdf;Integrated Security=True")) {
       conn.Open();

       using (SqlCommand cmd1 = new SqlCommand("INSERT INTO TBL_VIDEO(TIMESTAMP,JOY_SCORE,SURPRISE_SCORE,ANGER_SCORE,FEAR_SCORE,SADNESS_SCORE,DISGUST_SCORE) VALUES('" + has.getFrameTimeStamp().ToString() + "','" + people.get(i).impression.emotion_response.joy_score.ToString() + "','" + people.get(i).impression.emotion_response.surprise_score.ToString() + "','" + people.get(i).impression.emotion_response.anger_score.ToString() + "','" + people.get(i).impression.emotion_response.fear_score.ToString() + "','" + people.get(i).impression.emotion_response.sadness_score.ToString() + "','" + people.get(i).impression.emotion_response.disgust_score.ToString() + "')", conn)) {

          using (SqlDataReader dr = cmd1.ExecuteReader()) {
          }
       }
   }
}

60 seconds in 1 minute. So just divide your number of seconds by 60 to get the number of minutes? — Fuzzybear, Jul 30 '18 at 09:24
1. You need to format the code so that it is more readable. 2. Which variable in the code is representing the time to be saved in the database? 3. Your code is widely exposed to SQL injection. you need to learn about parameterized SQL queries and use it in your application. — Chetan, Jul 30 '18 at 09:25
you could, but why not just store it as seconds? Then when you _display_ it, you can decide to display at as seconds, minutes, hours, days, years or whatever is suitable in the context of the display. The smaller the unit you use for storage, the more flexibility you have later when viewing the data. If you really want to change it to minutes, then the answer to " Is there a way for me to convert the timing into minutes before inserting it into the database?" is yes, it's called maths...computers are really very good at it. I assume you know how many seconds are in a minute? — ADyson, Jul 30 '18 at 09:25
Here is some documentation about [TimeSpan](https://msdn.microsoft.com/en-us/library/system.timespan.minutes(v=vs.110).aspx). Becarefull on what you want in the database, you should store the real unformated value and let the display make it nice. — Drag and Drop, Jul 30 '18 at 09:27
If the values are less than 24 hours you could change the field's type to `time` and store the TimeSpan directly. The TimeSpan type also has properties that return the value as minutes, but you'll have to fix your query first. Using string concatenation exposes you to conversion problems with dates and decimal values, never mind the potential for SQL injection — Panagiotis Kanavos, Jul 30 '18 at 09:31
Save as a fraction of a Day (DateTime) : 300000/(24.0 * 3600.0). — jdweng, Jul 30 '18 at 09:37
It should be noted that if you store as minutes rather than seconds then you potentially will start to get floating point issues. ie if you have 10 seconds then in minutes that is 0.1666666 which is not accurately representable as a floating point number. This means that you would actually lose accuracy when storing as minutes as opposed to the raw seconds. — Chris, Jul 30 '18 at 09:50
Possible duplicate of [What is the best way to convert seconds into (Hour:Minutes:Seconds:Milliseconds) time?](https://stackoverflow.com/questions/463642/what-is-the-best-way-to-convert-seconds-into-hourminutessecondsmilliseconds) — Liam, Jul 30 '18 at 09:52

Kami · Accepted Answer · 2018-07-31T08:40:55.243

To answer your question, divide the value by 60000 (60 seconds as milliseconds) to get the desired result.

However, it would be better to store the value as numeric milliseconds and perform the conversion as need when displaying to the user.

// Constants
const int INTERVAL_1_MINUTES_AS_MICROSECONDS = 1 * 60 * 1000; // Or 60000
const int INTERVAL_5_MINUTES_AS_MICROSECONDS = 5 * 60 * 1000; // Or 300000


if (has.getFrameTimeStamp() % INTERVAL_5_MINUTES_AS_MICROSECONDS == 0)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();

        using (SqlCommand cmd1 = new SqlCommand())
        {
            cmd1.CommandText = @"INSERT INTO TBL_VIDEO(TIMESTAMP,JOY_SCORE,SURPRISE_SCORE,ANGER_SCORE,FEAR_SCORE,SADNESS_SCORE,DISGUST_SCORE)";
            cmd1.CommandText += " VALUES (@FrameTimeStamp, @emotion_response_joy, @emotion_response_surprise, @emotion_response_anger, @emotion_response_fear, @emotion_response_sadness, @emotion_response_disgust);";

            cmd1.CommandType = CommandType.Text;
            // Potentially adding a numeric value as string
            //cmd1.Parameters.Add(new SqlParameter("@FrameTimeStamp", (Math.Floor(has.getFrameTimeStamp() / INTERVAL_1_MINUTE_AS_MICROSECONDS)).ToString())); // Need to type check
            cmd1.Parameters.Add(new SqlParameter("@FrameTimeStamp", (has.getFrameTimeStamp()).ToString()));
            cmd1.Parameters.Add(new SqlParameter("@emotion_response_joy", people.get(i).impression.emotion_response.joy_score.ToString()));
            cmd1.Parameters.Add(new SqlParameter("@emotion_response_surprise", people.get(i).impression.emotion_response.surprise_score.ToString()));
            cmd1.Parameters.Add(new SqlParameter("@emotion_response_anger", people.get(i).impression.emotion_response.anger_score.ToString()));
            cmd1.Parameters.Add(new SqlParameter("@emotion_response_fear", people.get(i).impression.emotion_response.fear_score.ToString()));
            cmd1.Parameters.Add(new SqlParameter("@emotion_response_sadness", people.get(i).impression.emotion_response.sadness_score.ToString()));
            cmd1.Parameters.Add(new SqlParameter("@emotion_response_disgust", people.get(i).impression.emotion_response.disgust_score.ToString()));

            int rowsAffected = cmd1.ExecuteNonQuery();
        }
    }
}

Couple of points with the code sample. You are converting the timestamp to string to store the value. As a result it becomes difficult to perform arithmetical operations on the value as you will have to convert to a numeric form every time. Store the value as numeric (Long or another suitable type would be best).

In addition, you might want to use parameters to supply the values rather than using string concatenation.

Finally, there is no need to use a SqlDataReader when performing an insert operation as no records will be returned. Using ExecuteNonQuery is a better option.

TlDr; Divide by 60000. If you have a definite requirement to store the value as minutes, or store as milliseconds and perform the conversion in the display code. Use SQL parameters to supply data.

Saving time as minutes into database

1 Answers1