Re: Date/Time Difference Calculations
Bingo, Shawn wins the virtual beer(s). I never looked at the unix_timestamp function. Thanks to everyone else and wishing you virtual beers as well! Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc - Original Message - From: <[EMAIL PROTECTED]> To: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, June 11, 2004 14:54 Subject: Re: Date/Time Difference Calculations > > I didn't see where these were 4.1+ function so I think it will work. I > refer you to: > http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html > http://dev.mysql.com/doc/mysql/en/Cast_Functions.html (if you ever run into > 'negative' time differences) > > SELECT sec_to_time(unix_timestamp(transfer_end) - > unix_timestamp(transfer_start)) from queue; > > I know it will work for values up to 24 hours different. If sec_to_time is > using an internal TIME data type value you get just under 840 hours of > differential. > > HTH, > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date/Time Difference Calculations
I didn't see where these were 4.1+ function so I think it will work. I
refer you to:
http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html
http://dev.mysql.com/doc/mysql/en/Cast_Functions.html (if you ever run into
'negative' time differences)
SELECT sec_to_time(unix_timestamp(transfer_end) -
unix_timestamp(transfer_start)) from queue;
I know it will work for values up to 24 hours different. If sec_to_time is
using an internal TIME data type value you get just under 840 hours of
differential.
HTH,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Dirk Bremer
\(NISC\)"To: <[EMAIL PROTECTED]>
<[EMAIL PROTECTED]cc:
.cc> Fax to:
Subject: Re: Date/Time Difference
Calculations
06/11/2004 03:37
PM
Looking at the results further reveals that a numeric subtraction is being
performed on the two datetime fields rather than a date-type subtraction.
Any thoughts on how to perform a date subtraction in version 4.0.18?
Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters
USA Central Time Zone
636-922-9158 ext. 8652 fax 636-447-4471
[EMAIL PROTECTED]
www.nisc.cc
- Original Message -
From: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, June 11, 2004 14:29
Subject: Re: Date/Time Difference Calculations
> Here is an example using sec_to_time. Note that the results are
inconsistent
> and sometimes inaccurate. It seems that when the difference is less than
one
> minute, the result is correct, when it is over one minute, the result is
> incorrect.
>
> select ident,
> transfer_start,
> transfer_end,
>sec_to_time(transfer_end - transfer_start) as 'Transfer Time1',
>(transfer_end - transfer_start) as 'Transfer Time2'
> from queue
> where ident > 1300
> order by queue_time
> --
>
>
+---+-+-++--
> --+
> | ident | transfer_start | transfer_end| Transfer Time1 |
> Transfer Time2 |
>
+---+-+-++--
> --+
> | 1301 | 2004-06-10 09:32:26 | 2004-06-10 09:32:28 | 00:00:02 |
> 2 |
> | 1302 | 2004-06-10 09:33:26 | 2004-06-10 09:33:29 | 00:00:03 |
> 3 |
> | 1303 | 2004-06-10 13:00:38 | 2004-06-10 15:29:54 | 06:21:56 |
> 22916 |
> | 1304 | 2004-06-10 13:17:35 | 2004-06-10 13:19:31 | 00:03:16 |
> 196 |
> | 1305 | 2004-06-10 13:19:35 | 2004-06-10 13:19:37 | 00:00:02 |
> 2 |
> | 1306 | 2004-06-10 13:20:35 | 2004-06-10 13:20:38 | 00:00:03 |
> 3 |
> | 1307 | 2004-06-10 19:37:50 | 2004-06-10 19:47:00 | 00:15:50 |
> 950 |
> | 1308 | 2004-06-10 23:10:08 | 2004-06-10 23:10:23 | 00:00:15 |
> 15 |
> | 1309 | 2004-06-10 23:11:08 | 2004-06-11 00:00:01 | 213:34:53 |
> 768893 |
> | 1311 | 2004-06-10 23:07:08 | 2004-06-10 23:08:00 | 00:01:32 |
> 92 |
> | 1310 | 2004-06-10 23:08:58 | 2004-06-10 23:08:58 | 00:00:00 |
> 0 |
> | 1312 | 2004-06-11 08:58:30 | 2004-06-11 09:19:04 | 01:41:14 |
> 6074 |
> | 1315 | 2004-06-11 09:01:30 | 2004-06-11 09:02:05 | 00:01:15 |
> 75 |
> | 1313 | 2004-06-11 09:03:30 | 2004-06-11 09:23:44 | 00:33:34 |
> 2014 |
> | 1314 | 2004-06-11 09:24:32 | 2004-06-11 09:24:45 | 00:00:13 |
> 13 |
>
+---+-+-++--
> --+
> 15 rows in set (0.00 sec)
>
> Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters
> USA Central Time Zone
> 636-922-9158 ext. 8652 fax 636-447-4471
>
> [EMAIL PROTECTED]
> www.nisc.cc
>
> > You probably want SEC_T
Re: Date/Time Difference Calculations
Close, but time_to_sec requires a time argument, not a datetime argument. My next iteration is: select ident, transfer_start, transfer_end, sec_to_time(time_to_sec(substring(transfer_end,12,8)) - time_to_sec(substring(tra cast(transfer_end - transfer_start as signed) as 'Transfer Time2' from queue where ident > 1300 order by queue_time -- +---+-+-++-- --+ | ident | transfer_start | transfer_end| Transfer Time1 | Transfer Time2 | +---+-+-++-- --+ | 1301 | 2004-06-10 09:32:26 | 2004-06-10 09:32:28 | 00:00:02 | 2 | | 1302 | 2004-06-10 09:33:26 | 2004-06-10 09:33:29 | 00:00:03 | 3 | | 1303 | 2004-06-10 13:00:38 | 2004-06-10 15:29:54 | 02:29:16 | 22916 | | 1304 | 2004-06-10 13:17:35 | 2004-06-10 13:19:31 | 00:01:56 | 196 | | 1305 | 2004-06-10 13:19:35 | 2004-06-10 13:19:37 | 00:00:02 | 2 | | 1306 | 2004-06-10 13:20:35 | 2004-06-10 13:20:38 | 00:00:03 | 3 | | 1307 | 2004-06-10 19:37:50 | 2004-06-10 19:47:00 | 00:09:10 | 950 | | 1308 | 2004-06-10 23:10:08 | 2004-06-10 23:10:23 | 00:00:15 | 15 | | 1309 | 2004-06-10 23:11:08 | 2004-06-11 00:00:01 | -23:11:07 | 768893 | | 1311 | 2004-06-10 23:07:08 | 2004-06-10 23:08:00 | 00:00:52 | 92 | | 1310 | 2004-06-10 23:08:58 | 2004-06-10 23:08:58 | 00:00:00 | 0 | | 1312 | 2004-06-11 08:58:30 | 2004-06-11 09:19:04 | 00:20:34 | 6074 | | 1315 | 2004-06-11 09:01:30 | 2004-06-11 09:02:05 | 00:00:35 | 75 | | 1313 | 2004-06-11 09:03:30 | 2004-06-11 09:23:44 | 00:20:14 | 2014 | | 1314 | 2004-06-11 09:24:32 | 2004-06-11 09:24:45 | 00:00:13 | 13 | +---+-+-++-- --+ 15 rows in set (0.01 sec) Which appears to be working correctly (Transfer Time1) except for ident = 1309, which spans a date boundary. So, back to the drawing board to figure out the date boundary issue. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc - Original Message - From: "gerald_clark" <[EMAIL PROTECTED]> To: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, June 11, 2004 14:50 Subject: Re: Date/Time Difference Calculations > select sec_to_time(time_to_sec(transfer_end)-time_to_sec(transfer_start)); > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date/Time Difference Calculations
select sec_to_time(time_to_sec(transfer_end)-time_to_sec(transfer_start)); Dirk Bremer (NISC) wrote: Looking at the results further reveals that a numeric subtraction is being performed on the two datetime fields rather than a date-type subtraction. Any thoughts on how to perform a date subtraction in version 4.0.18? Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc - Original Message - From: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, June 11, 2004 14:29 Subject: Re: Date/Time Difference Calculations Here is an example using sec_to_time. Note that the results are inconsistent and sometimes inaccurate. It seems that when the difference is less than one minute, the result is correct, when it is over one minute, the result is incorrect. select ident, transfer_start, transfer_end, sec_to_time(transfer_end - transfer_start) as 'Transfer Time1', (transfer_end - transfer_start) as 'Transfer Time2' from queue where ident > 1300 order by queue_time -- +---+-+-++-- --+ | ident | transfer_start | transfer_end| Transfer Time1 | Transfer Time2 | +---+-+-++-- --+ | 1301 | 2004-06-10 09:32:26 | 2004-06-10 09:32:28 | 00:00:02 | 2 | | 1302 | 2004-06-10 09:33:26 | 2004-06-10 09:33:29 | 00:00:03 | 3 | | 1303 | 2004-06-10 13:00:38 | 2004-06-10 15:29:54 | 06:21:56 | 22916 | | 1304 | 2004-06-10 13:17:35 | 2004-06-10 13:19:31 | 00:03:16 | 196 | | 1305 | 2004-06-10 13:19:35 | 2004-06-10 13:19:37 | 00:00:02 | 2 | | 1306 | 2004-06-10 13:20:35 | 2004-06-10 13:20:38 | 00:00:03 | 3 | | 1307 | 2004-06-10 19:37:50 | 2004-06-10 19:47:00 | 00:15:50 | 950 | | 1308 | 2004-06-10 23:10:08 | 2004-06-10 23:10:23 | 00:00:15 | 15 | | 1309 | 2004-06-10 23:11:08 | 2004-06-11 00:00:01 | 213:34:53 | 768893 | | 1311 | 2004-06-10 23:07:08 | 2004-06-10 23:08:00 | 00:01:32 | 92 | | 1310 | 2004-06-10 23:08:58 | 2004-06-10 23:08:58 | 00:00:00 | 0 | | 1312 | 2004-06-11 08:58:30 | 2004-06-11 09:19:04 | 01:41:14 | 6074 | | 1315 | 2004-06-11 09:01:30 | 2004-06-11 09:02:05 | 00:01:15 | 75 | | 1313 | 2004-06-11 09:03:30 | 2004-06-11 09:23:44 | 00:33:34 | 2014 | | 1314 | 2004-06-11 09:24:32 | 2004-06-11 09:24:45 | 00:00:13 | 13 | +---+-+-++-- --+ 15 rows in set (0.00 sec) Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc You probably want SEC_TO_TIME: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html SELECT a as start, b as end, SEC_TO_TIME(end - start) FROM table Eamon Daly - Original Message - From: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, June 11, 2004 1:57 PM Subject: Date/Time Difference Calculations I'm using MySQL version 4.0.18. I have two datetime columns in the same table, one that represents a start time and the other that represents an end time. I would like to write a query that will show the difference between these two columns in a HH:MM:SS format. The values of the two columns as inserted into the table are never be separated by more than a few hours, but could span a day boundary, i.e the start time could be late one day end the end time early the next day, so the date will have to be taken into consideration for the calculation. Looking at the docs, it appears that version 4.1.x has a lot more date/time functions, but I'm wondering if something similar can be arrived at under the version that I am using. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date/Time Difference Calculations
Looking at the results further reveals that a numeric subtraction is being performed on the two datetime fields rather than a date-type subtraction. Any thoughts on how to perform a date subtraction in version 4.0.18? Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc - Original Message - From: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, June 11, 2004 14:29 Subject: Re: Date/Time Difference Calculations > Here is an example using sec_to_time. Note that the results are inconsistent > and sometimes inaccurate. It seems that when the difference is less than one > minute, the result is correct, when it is over one minute, the result is > incorrect. > > select ident, > transfer_start, > transfer_end, >sec_to_time(transfer_end - transfer_start) as 'Transfer Time1', >(transfer_end - transfer_start) as 'Transfer Time2' > from queue > where ident > 1300 > order by queue_time > -- > > +---+-+-++-- > --+ > | ident | transfer_start | transfer_end| Transfer Time1 | > Transfer Time2 | > +---+-+-++-- > --+ > | 1301 | 2004-06-10 09:32:26 | 2004-06-10 09:32:28 | 00:00:02 | > 2 | > | 1302 | 2004-06-10 09:33:26 | 2004-06-10 09:33:29 | 00:00:03 | > 3 | > | 1303 | 2004-06-10 13:00:38 | 2004-06-10 15:29:54 | 06:21:56 | > 22916 | > | 1304 | 2004-06-10 13:17:35 | 2004-06-10 13:19:31 | 00:03:16 | > 196 | > | 1305 | 2004-06-10 13:19:35 | 2004-06-10 13:19:37 | 00:00:02 | > 2 | > | 1306 | 2004-06-10 13:20:35 | 2004-06-10 13:20:38 | 00:00:03 | > 3 | > | 1307 | 2004-06-10 19:37:50 | 2004-06-10 19:47:00 | 00:15:50 | > 950 | > | 1308 | 2004-06-10 23:10:08 | 2004-06-10 23:10:23 | 00:00:15 | > 15 | > | 1309 | 2004-06-10 23:11:08 | 2004-06-11 00:00:01 | 213:34:53 | > 768893 | > | 1311 | 2004-06-10 23:07:08 | 2004-06-10 23:08:00 | 00:01:32 | > 92 | > | 1310 | 2004-06-10 23:08:58 | 2004-06-10 23:08:58 | 00:00:00 | > 0 | > | 1312 | 2004-06-11 08:58:30 | 2004-06-11 09:19:04 | 01:41:14 | > 6074 | > | 1315 | 2004-06-11 09:01:30 | 2004-06-11 09:02:05 | 00:01:15 | > 75 | > | 1313 | 2004-06-11 09:03:30 | 2004-06-11 09:23:44 | 00:33:34 | > 2014 | > | 1314 | 2004-06-11 09:24:32 | 2004-06-11 09:24:45 | 00:00:13 | > 13 | > +---+-+-++-- > --+ > 15 rows in set (0.00 sec) > > Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters > USA Central Time Zone > 636-922-9158 ext. 8652 fax 636-447-4471 > > [EMAIL PROTECTED] > www.nisc.cc > > > You probably want SEC_TO_TIME: > > > > http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html > > > > SELECT a as start, b as end, SEC_TO_TIME(end - start) FROM table > > > > > > Eamon Daly > > > > > > > > - Original Message - > > From: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Friday, June 11, 2004 1:57 PM > > Subject: Date/Time Difference Calculations > > > > > > > I'm using MySQL version 4.0.18. I have two datetime columns in the same > > > table, one that represents a start time and the other that represents an > > end > > > time. I would like to write a query that will show the difference > between > > > these two columns in a HH:MM:SS format. The values of the two columns as > > > inserted into the table are never be separated by more than a few hours, > > but > > > could span a day boundary, i.e the start time could be late one day end > > the > > > end time early the next day, so the date will have to be taken into > > > consideration for the calculation. Looking at the docs, it appears that > > > version 4.1.x has a lot more date/time functions, but I'm wondering if > > > something similar can be arrived at under the version that I am using. > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date/Time Difference Calculations
Here is an example using sec_to_time. Note that the results are inconsistent and sometimes inaccurate. It seems that when the difference is less than one minute, the result is correct, when it is over one minute, the result is incorrect. select ident, transfer_start, transfer_end, sec_to_time(transfer_end - transfer_start) as 'Transfer Time1', (transfer_end - transfer_start) as 'Transfer Time2' from queue where ident > 1300 order by queue_time -- +---+-+-++-- --+ | ident | transfer_start | transfer_end| Transfer Time1 | Transfer Time2 | +---+-+-++-- --+ | 1301 | 2004-06-10 09:32:26 | 2004-06-10 09:32:28 | 00:00:02 | 2 | | 1302 | 2004-06-10 09:33:26 | 2004-06-10 09:33:29 | 00:00:03 | 3 | | 1303 | 2004-06-10 13:00:38 | 2004-06-10 15:29:54 | 06:21:56 | 22916 | | 1304 | 2004-06-10 13:17:35 | 2004-06-10 13:19:31 | 00:03:16 | 196 | | 1305 | 2004-06-10 13:19:35 | 2004-06-10 13:19:37 | 00:00:02 | 2 | | 1306 | 2004-06-10 13:20:35 | 2004-06-10 13:20:38 | 00:00:03 | 3 | | 1307 | 2004-06-10 19:37:50 | 2004-06-10 19:47:00 | 00:15:50 | 950 | | 1308 | 2004-06-10 23:10:08 | 2004-06-10 23:10:23 | 00:00:15 | 15 | | 1309 | 2004-06-10 23:11:08 | 2004-06-11 00:00:01 | 213:34:53 | 768893 | | 1311 | 2004-06-10 23:07:08 | 2004-06-10 23:08:00 | 00:01:32 | 92 | | 1310 | 2004-06-10 23:08:58 | 2004-06-10 23:08:58 | 00:00:00 | 0 | | 1312 | 2004-06-11 08:58:30 | 2004-06-11 09:19:04 | 01:41:14 | 6074 | | 1315 | 2004-06-11 09:01:30 | 2004-06-11 09:02:05 | 00:01:15 | 75 | | 1313 | 2004-06-11 09:03:30 | 2004-06-11 09:23:44 | 00:33:34 | 2014 | | 1314 | 2004-06-11 09:24:32 | 2004-06-11 09:24:45 | 00:00:13 | 13 | +---+-+-++-- --+ 15 rows in set (0.00 sec) Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc > You probably want SEC_TO_TIME: > > http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html > > SELECT a as start, b as end, SEC_TO_TIME(end - start) FROM table > > > Eamon Daly > > > > - Original Message - > From: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Friday, June 11, 2004 1:57 PM > Subject: Date/Time Difference Calculations > > > > I'm using MySQL version 4.0.18. I have two datetime columns in the same > > table, one that represents a start time and the other that represents an > end > > time. I would like to write a query that will show the difference between > > these two columns in a HH:MM:SS format. The values of the two columns as > > inserted into the table are never be separated by more than a few hours, > but > > could span a day boundary, i.e the start time could be late one day end > the > > end time early the next day, so the date will have to be taken into > > consideration for the calculation. Looking at the docs, it appears that > > version 4.1.x has a lot more date/time functions, but I'm wondering if > > something similar can be arrived at under the version that I am using. > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date/Time Difference Calculations
You probably want SEC_TO_TIME: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html SELECT a as start, b as end, SEC_TO_TIME(end - start) FROM table Eamon Daly - Original Message - From: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, June 11, 2004 1:57 PM Subject: Date/Time Difference Calculations > I'm using MySQL version 4.0.18. I have two datetime columns in the same > table, one that represents a start time and the other that represents an end > time. I would like to write a query that will show the difference between > these two columns in a HH:MM:SS format. The values of the two columns as > inserted into the table are never be separated by more than a few hours, but > could span a day boundary, i.e the start time could be late one day end the > end time early the next day, so the date will have to be taken into > consideration for the calculation. Looking at the docs, it appears that > version 4.1.x has a lot more date/time functions, but I'm wondering if > something similar can be arrived at under the version that I am using. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
