CODEWITHSUNDEEP
mysqldatetime
Lazarus Thurston
Lazarus Thurston

Reputation: 1287

Why do I get NULL results with the MySQL function TIME_TO_SEC?

I have a MySQL table with two datetime columns. I want to create one new generated column that contains time difference of the two datetime columns. I am using the MySQL function TIME_TO_SEC to convert the difference to seconds.

Strangely some of the rows return a NULL, and I am unable to understand why. Here are the first 25 rows of the table..

 class_session_id starts_on           ends_on              session_time_computed
                8 2014-12-24 03:35:47 2014-12-24 05:07:33                    NA
                9 2014-12-24 05:50:37 2014-12-24 07:10:07                    NA
               10 2014-12-24 07:18:51 2014-12-24 08:52:27                    NA
               11 2014-12-19 09:00:41 2014-12-19 10:35:54                  5713
               12 2014-12-19 10:45:29 2014-12-19 11:55:48                  4219
               13 2014-12-24 12:00:00 2014-12-24 14:00:00                  7200
               14 2014-12-24 14:32:10 2014-12-24 14:47:18                   908
               15 2014-12-24 14:48:13 2014-12-25 08:29:51                    NA
               16 2014-12-15 04:06:13 2014-12-15 04:20:41                   868
               17 2014-12-15 07:09:27 2014-12-15 08:20:40                  4273
               18 2014-12-15 06:42:28 2014-12-15 06:51:43                   555
               28 2014-11-25 11:18:00 2014-11-25 09:30:00                    NA
               29 2014-11-27 10:18:00 2014-11-27 11:30:00                  4320
               30 2014-11-26 08:18:00 2014-11-26 09:30:00                  4320
               34 2015-01-08 10:41:52 2015-01-08 11:00:00                  3528
               37 2014-12-02 12:44:36 2014-12-02 14:44:00                    NA
               49 2014-11-02 12:44:00 2014-11-02 14:44:00                  7200
               54 2014-12-07 07:05:35 2014-12-07 07:50:00                    NA
               55 2014-12-07 10:00:00 2014-12-07 11:00:00                  3600
               56 2014-11-12 12:57:00 2014-11-12 17:00:00                 16980
               82 2014-12-05 14:29:18 2014-12-05 16:00:00                    NA
               83 2014-12-05 16:29:18 2014-12-05 17:00:00                    NA
               84 2014-12-05 17:49:17 2014-12-05 18:25:00                    NA
               85 2014-12-06 15:00:17 2014-12-06 16:00:00                    NA
               87 2014-12-06 16:25:00 2014-12-06 16:59:00                  2040`

I use the following script to generate the last column.

 ALTER TABLE class_sessions_archive
        MODIFY COLUMN session_time_computed int(11) AS (TIME_TO_SEC(ends_on - starts_on));

Why do I get NULL results for so many rows? Here is the structure of the table :

           Field     Type Null Key Default             Extra
      class_session_id  int(11)   NO PRI    <NA>    auto_increment
              class_id  int(11)   NO MUL    <NA>                  
               room_id  int(11)   NO MUL    <NA>                  
            teacher_id  int(11)   NO MUL    <NA>                  
             starts_on datetime   NO        <NA>                  
               ends_on datetime   NO        <NA>                  
         session_state  int(11)   NO MUL    <NA>                  
         stud_attended int(100)  YES        <NA>                  
 total_stud_registered  int(11)   NO           0                  
          session_time  int(11)   NO           0                  
                gi_num  int(11)   NO           0                  
                gi_den  int(11)   NO           0                  
                    pi  int(11)   NO           0                  
 session_time_computed  int(11)  YES        <NA> VIRTUAL GENERATED

Upvotes: 1

Views: 536

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Now that I think about it, you normally convert the time to seconds before subtracting:

ALTER TABLE class_sessions_archive
        MODIFY COLUMN session_time_computed int(11) AS
            (TO_SECONDS(ends_on) - TO_SECONDS(starts_on));

Otherwise, you are limited by the range of values for the time data type -- which is probably why you are getting NULL.

Upvotes: 1

Related Questions