Sum Total Of A Time Field
We have a table with a field of type TEXT storing the time for certain actions (ie. 0:30 - for 0 mins 30 secs, 1:20:00 - for 1 hr 20 mins 0secs).
We've come to a point where we'd like to sum the entire field up and get back the total time. I wish we established this filed as a time data type rather than TEXT but that's the way its set up. How would you go about achieving this?
Perhaps I need to convert the TEXT to some other format to get back a more usable value for my PHP code to use?
View Complete Forum Thread with Replies
Related Forum Messages:
Tracking The Total Time Each Day A User Spends On Site
My DB is currently setup like this: SQL Code: id   userid   timecheck      visit_type 1    255   1205896013     IN 2    255   1205896017     refresh 3    255   1205896025     refresh 2    255   1205896150     IN I basically need to grab the difference between the time from the first IN of the day to the row marked refresh just before the next in, and then repeat wherever the value in the timecheck column = the same day. PHP Code: $sql= "select * from timetable where timecheck = '".date("M d, Y", $row['timecheck'])' and visit_type between 'IN' and 'refresh'";
View Replies !
Is There A Command For Getting Total Values Of A Field?
Is there a MySQL command to select the contents of a field and then add the values together to get the total value? I mean lets say we have a field called: X And that field can contain any value in there, from 1 to whatever. And we want to get the total values that are in that field for the selected range. So if I use SELECT COUNT(news_flag) FROM advert_impressions WHERE DATE > '2008-03-10' AND news_flag > 0; this gives me the total count of rows that i have matching above WHERE condition but it does not give me the total value of the numbers in that field for selected date range. What is the MySQL command for getting such total value?
View Replies !
Field Type :: Time Field With Decimal
I want to add times into a field in the form mm:ss.d such as 03:33.5 but when I use the time type it removes the decimal but I really need it to keep it in. At the moment I am using a char(10) type and converting it in my scripts. Is there a way I can add it in using the correct type? I couldn't see anything in the documentation on the time type except that you could enter it in the form I want but it will convert it.
View Replies !
Date Field, Time Field, Sort By Date And Time
I have a MySQL date field (e.g 13/12/2006) and a MySQL time field (e.g.13:00) in the same row. I'd like to join these two fields to make a date/time field (e.g. 13:00 13/12/2006) on the fly and to be able to sort the query results on the resulting field.
View Replies !
Calcuate Row Total And Its Cummulative Total
1. I hava a table structure like. EID Day1 Day2 Day3 RowTotal CumTotal And Results will be EID Day1 Day2 Day3 RowTotal CumTotal 1 1 0 0 1 1 1 0 1 0 1 2 1 0 0 1 1 3 How to calculate RowTotal and CumTotal in INSERT or UPDATE or SELECT Statement 2. I hava a table structure like. EID Day1 Day2 Day3 1 1 0 0 1 0 1 0 1 0 0 1 But I want after SELECT Statements Executes in the Following EID Day1 Day2 Day3 RowTotal CumTotal 1 1 0 0 1 1 1 0 1 0 1 2 1 0 0 1 1 3 How to calculate RowTotal and CumTotal in SELECT Statement
View Replies !
Date Field, Time Field
I have a MySQL date field (e.g 13/12/2006) and a MySQL time field (e.g.13:00) in the same row. I'd like to join these two fields to make a date/time field (e.g. 13:00 13/12/2006) and to be able to sort the query results on the resulting field.
View Replies !
Time As A Field
Can we use a trigger to write to a field in a table? My problem is that I need the time in seconds (or minutes) since the system started. What I need specifically is that every time a record is written I can find out the time since the start of the system and put that value (in seconds) alongside the record itself.
View Replies !
Store AM/PM In Time Field.
I have field type time. I do PHP Code: insert into mytable(mytime) values('06:30:00 PM') . PHP Code: Select * from mytable shows 06:30:00 (without PM). Is there a way to store AM/PM ?
View Replies !
TIME Field And NOW() Insert
Code: $updatesql ="update tblclientdetails set loginfailed ='0', logged='Yes', timeslogged = timeslogged +1, dtlogged = now(), tmlogged = now() where username='$username'";
View Replies !
TIME Field Type
Within PHP I have the time data of 01:32 or 1:32 - this refers to a mm:ss / m:ss format. My MySQL Field type is a TIME field - I require it to be a time format so I can perform functions upon the row. The time format is 00:00:00 (hh:mm:ss) - is there any way of having the field correspond with my format, or is there a way of converting it upon entry. If not it means I'll have to use string functions in PHP to convert the data into the neccessary format - which frankly is a pain the ***. Then again, I'm confident there's a solution available in MySQL, and I'm uber-confident that you guys will be the people to ask too.
View Replies !
Datetime Field Updateing Time Only
How would I update the time only on a datetime field? The filed is: "2008-09-14 12:06:00" and I want to change it to "2008-09-14 12:16:00". If I use  update field_name set field_name = TIMEDIFF('2008-09-14 12:06:00', '2008-09-14 12:16:00');Â
View Replies !
Update Time Ony In Datetime Field
How do I update the time portion in a datetime type field? There are several rows containing various times and I want to make them all the same: 11:30:00. I have tried doing the following w/o success: update field set field = "date(field) 02:02:00"
View Replies !
Date/time Field Type
I want a field to automatically record the date/time each time a record is written to the db. Do I want that field to be of type DATE, DATETIME, TIMESTAMP…? My book is less than clear.
View Replies !
Update Without Chnging Time Field
I have a table named Test as following id int(2), time_insert timestamp, key_check int(2), primary_key (id)); In certain period of time, i want to update key_check field. update Test set key_check=2; When i insert the update cmd, both time_insert and key_check change. I just wanna change the value of key_check not time_insert. How to do this?
View Replies !
Problem Updating Time Type Field
Here's a query I tried and doesn't work for me. UPDATE activities SET ljTime=19:00:00 WHERE actID=05052003 I have also tried this: UPDATE activities SET ljTime=07:00:00 [PM] WHERE actID=05052003 But I still receive the error: [txoov] ERROR 1064: You have an error in your SQL syntax near ':00:00 WHERE actID=05052003' at line 1.
View Replies !
Selecting Date Time Field, Difference In Minutes
I wonder if is there any way to select from a table all the records which has a Date Field that is at least five minutes old? In other words, I have a table with a date field and I need to select all the records that are older than five minutes, has their date field updated before five minutes. Usually I do it with days by using the to_days() function and comparing the date to now: (to_dayss(now()) - to_days(somedate)).
View Replies !
Sort By Time In Text String Field (hh:mm:am/pm Format)
I have a group of records that I need to sort by time field. The time field is a text string (should've been a date/time - I know -doh!) I was wondering if anyone knew how to write a query to sort in Ascending order with the earliest appearing first. The times are stored as follows: 10:30pm 11:30pm etc.
View Replies !
Converting A TIME Field Into Seconds : Error Message
CREATE TABLE `m010` (`date` date NOT NULL, `hour` time NOT NULL, `duration` time default NULL, `cs` int(11) default NULL, `id` bigint(20) NOT NULL auto_increment, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC the field 'duration' is in TIME format (thus 00:00:00) .. I want to convert it in seconds. I've read that I can do something like this: mysql> select duration, TIME_TO_SEC (m010.duration) AS 'ts' from m010 WHERE id = 736; Problem is, I get this error : ERROR 1305 (42000): FUNCTION m010.TIME_TO_SEC does not exist
View Replies !
Generating DAILY Min&Max Over A Period Of Time, Based On A "DateTime" Field
how to print the minimum and maximum of a specific field, between two datetimes (both having the time 02:00:00) - so I did this: WHERE ((tblvalues.dtLPDateTime)>="2006-01-01 02:00:00" And (tblvalues.dtLPDateTime)<"2006-01-03 02:00:00")) Now I want to print those min and max values but for each day in this cycle, i mean, for this example, there would be two records like: Day Min Max 2006-01-01 02:00:00 -> 2006-01-02 02:00:00 | 40 | 59 2006-01-02 02:00:00 -> 2006-01-03 02:00:00 | 49 | 68 where at this point i`m getting min=40 and max=68
View Replies !
PHP/MySQL Sorting By Date & Time (using Non Military Time)
This is probably a simple issue but I have searched online and can't find an answer. I am using PHP/MySQL and I guess the most intuitive would be to have three select boxes containing HOUR / MINUTE / AM,PM option. Then store the time into mysql using there functions so I can output the data sorting them all by date and time. All the examples I have seen are for military time only but I'm sure there has got to be a simple mysql function or php function that converts non military to military and a formatting function to display with the AM / PM. But I have not found it.
View Replies !
Compare Time Posted With Current Time Not Working
May I know how to get records with interval of 1 hour in database where the posting time is in this format '2008-05-15 00:10:40' I tried with this : $query="SELECT date,name FROM message WHERE (timediff(date,NOW())<=CRUDATE() CURTIME())"; But is not working. May I know what is the correct way of doing it?
View Replies !
Add A Column To Get A Total
I need to know how to add a column of numbers and get a total using my sql command. SELECT v.vid, title, purchdate, sum(cost) AS Total FROM Video v, Vidcopy vc WHERE vid.v = vid.vc I need to know how to add the cost column and get a total.
View Replies !
Total Numbers
I need to count total number of records present in 2 tables. I am having a working query for SQL Server but it doesn't work in mySQL. select sum(a.total1) from (select count(*) total1 from CoreJava_Code union all select count(*) total from CoreJava_File)a
View Replies !
Counting Total Row
I am trying to select the total row count from two different tables. I am getting a result back but it is returning the same count for both even though they have different amounts. This is what I have so far:- SELECT COUNT(companies.comp_id) as tcustomers, COUNT(project_id) as tprojects FROM companies, projects as you can see I have two tables, companies and projects.
View Replies !
Grand Total
I have a site (php/mysql) which people register on, say how many people work in their firm and how much volunteering they are going to do by quarter. The relevant fields are q1,q2,q3 and q4 (the four quarters they volunteer for) and staff (the number of volunteers). The code I have is: SELECT firm, q1+q2+q3+q4 as total FROM hours INNER JOIN contact ON contact.username=hours.username Now what I want to do is divide the total I get for each firm by the number of staff. Also, I would like to find the grand total by adding all of the totals.
View Replies !
Running Total
I came across a question earlier on, where i thought they wanted to order by a count in the query and keep a running total. Which it turned out they didn't.. However, that left me wondering.. Is it possible in MySQL? I'm using MySQL 4.1.22. I have set up a basic test table ( this is just a play, this isn't a serious project, i'm just wondering!) which has the following data / layout on creation: Code: ....
View Replies !
TOTAL For Each Agent
I have wrote an SQL to get some information from a database that lists all agents and how long they have worked on a task in a particular queue. Example AGENT | WORK DONE | QUEUE A | 2 | GROUP 1 A | 3 | GROUP 2 B | 1 | GROUP 1 What I would like to do is write a query that will add a row to total the number of work AGENT A has done e.g. AGENT | WORK DONE | QUEUE A | 2 | GROUP 1 A | 3 | GROUP 2 A | 5 | B | 1 | GROUP 1 Is this possible?
View Replies !
Total Up Fields
Is there an easy way to do a query that will total up fields froms several different records? For example... If my table has this data... ID AMOUNT 1 45 2 51 3 9 4 17 5 15 I want to be able to do a query that will add up all the numbers in the "Amount" column. Can this be done?
View Replies !
Sub Total Query
I am pretty new to this stuff. I am trying to total the quantity colum in a table but on for each group. The quantity contains multiple instances of on certain part number and I group the results by part number. I need to get a total quantity from this. Here is what I have....
View Replies !
Rows And Total
I would like to return the following recordset and also get the total without having to run through the table first.. possible? $query = "SELECT SUM(h.Time) as projecttime, h.Date, h.Time, h.Message, c.No as CNo, c.Customer, p.Title, p.No as PNo FROM projects_history as h RIGHT JOIN projects as p ON h.Project = p.No RIGHT JOIN customers as c ON p.Customer = c.No WHERE h.Date BETWEEN '$thestart' AND '$theend' AND h.Deleted = 0 AND h.Time > 0 AND h.User in ($theusers) AND (c.No = $customer OR $customer = 0) AND (p.No = $project OR $project = 0) GROUP BY c.No, p.No ORDER BY c.No, p.No ASC"; $res = @mysql_query($query) or die(mysql_error()); while($row = @mysql_fetch_assoc($res)){ $totaltime += $row["projecttime"]; } $res = @mysql_query($query) or die(mysql_error()); while($row = @mysql_fetch_assoc($res)){ ... }
View Replies !
Total Sum Of Values?
this isn't a question of "how do I do this" but more of a "what's the best efficient way of doing this" the current code is: $result=mysql_query("SELECT votes FROM list"); votes is always a numerical value what's the most efficient way to add the total value of the votes?
View Replies !
Total Export In SQL
I need to have a total backup in SQL commands of a SQL-driven datasource. I mean, data and structure. I know that phpmyadmin can quickly deliver those means in a lone command, and of course I could backup the entire base if I only had phpmyadmin or a remote access mounted on the datasource. Simply, I dont have that and the customer's situation wont change. I only have my php and ftp access, in order to input some query into the datasource, so it pumps me all the data and structure.
View Replies !
Count Total Occurrence
I have a table that contains a number of columns, two of which are: name (varchar, text) type (varchar, text) I can't get my head around how to get the count of the total occurrence of a particular type (where type = 'sometype') of the 'name' who has saved the most of that particular 'type's.
View Replies !
Calculate Total Ip Address
I have a database which is full of: ip address - uploaded - downloaded. What i am trying to do is find the totals for each ip address (repeats of the same ip gives the sum on the uploaded/downloaded) and then order the query by the ratio (total upload/total download). But im having difficulties constructing the query which will get the sums of uploads and downloads from the same ip and also order by the query without getting errors (divide by zero when total download is 0).
View Replies !
Count Total BBS Posts
Hello, I have a small problem. I have two tables running the BBS posts and topics. The structure is as follows: BBS Posts post_id,topic_id,author_id,emoticon_id,post_field,uploaded_pic,post_date BBS Topics topic_id,topic_views,post_id,forum_id,topic_name,topic_author,topic_posted,topic_locked I want to do a total post count for each forum_id, and I can't figure out how to tie those two together.
View Replies !
Subtotal Total Same Query
trying to run this query php program, is it possible or practical? query works if i take out line "sum(subtotal) as calcTotal" otherwise it errors out. any other way i can calculate total of all the items? $sql = $connection->query("select sum((itemProdPrice * itemProdSize)*itemProdQty ) as subtotal, sum(subtotal) as calcTotal from items where itemOrdId = {$_SESSION['order_no']} and itemCustId = '$custId' ");
View Replies !
Total Like Records Query
Ime probably missing the boat here so please be patient. I am trying to create a query to display the total number of records with the same parent name. Eg Total records for author Eddings = 10 So in effect i want to count all the entries and display only the numeric figure of books for that author. I think its supposed to look something like: Select sum(Eddings) $total From books
View Replies !
Total Of A Multidimensional Array
I have a multidimensional array... while($row = mysql_fetch_array($query)) { $array[$row['type']] = $row; } Later on I display the value for type 'session' in a table... <? echo $array['session']['count']; ?> Question... I am now expanding the query that will include more values in the array (i.e. month to date rather than just a particular date)... How can I display the total value for this array (not the count)?
View Replies !
Getting Total Row Count When Using LIMIT
I'm having trouble explaining this to some of my friends at the moment so hopefully I'll be get across what I'm trying to do to someone in here =) Basically I'm running a site at the moment where I display a fair amount of data, enough where I have to display it over multiple pages. To do this at the moment I'm running a query on my information once to get the total number of records, then using this to get total page numbers followed by running the same query again but using LIMIT to get the data for the individual page I'm looking at. What I'm trying to do is speed things up by removing the need for running the two seperate queries. So I'm looking for a way to get the maximum number of results possible, but only return a limited amount of them. Is this possible, or is anyone aware of a work around to achieve the same thing?
View Replies !
|
TRACKER
