Inserting seconds into a database in HH:MM:SS format

Question

I have a JavaScript timer on a PHP page. It returns a number, representing seconds, that I want to insert into a database column of type Time. How can I do that?

This code is the best I could find:

$query = "INSERT INTO #__quiz_r_student_question (c_stu_quiz_id,
                                                  c_question_id,
                                                  c_score,
                                                  c_attempts,
                                                  is_correct,
                                                  time_to_solve
                                                 )" .
         "\n VALUES('".$stu_quiz_id."',
                    '".$quest_id."',
                    '".$c_quest_score."',
                    '".($c_quest_cur_attempt + 1)."',
                    '".$is_correct."',
                    DATEADD(ms, ".$time_to_solve."* 1000, 0)
                   )";

There is no standard SQL way to do what you want to do. This is something which varies depending on the DBMS you're using, so you should probably tell us which one you're using. — Keith Irwin, Nov 30 '11 at 21:47
What database platform are you using? SQL Server? Oracle? MySQL? Btrieve? — Mike Mooney, Nov 30 '11 at 21:47
Also, watch out for that SQL Injection vulnerability you have there... — Adriano Carneiro, Nov 30 '11 at 21:48
Well it's all data retrieved from the DB and not from the user, is it still vulnerabul to SQL Injection? — eric.itzhak, Nov 30 '11 at 21:50
Sure, if someone else has permission to put things in your database. — Pops, Nov 30 '11 at 22:18
If you're inserting something into the database without [prepared statements](http://en.wikipedia.org/wiki/SQL_injection) then you're (at least *potentially*) vulnerable to SQL injection. Obligatory [SQL-injection XKCD](http://xkcd.com/327/), and link to further reading [on Stackoverflow](http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php). — David Thomas, Nov 30 '11 at 22:20
At Stack Overflow, we don't typically update questions with the eventual solutions. Please post your fix as an answer instead. That way, you can accept it, which is semantically good, and you're more likely to get reputation because other users can judge the question and answer separately. — Pops, Nov 30 '11 at 22:28

score 3 · Accepted Answer · edited Dec 01 '11 at 16:15

3

This answer was originally posted by the asker as an edit to the question.

I solved my problem with PHP, not an SQL query. I used this code to change my seconds value into a readable time ready for database insertion:

$time = gmdate('H:i:s',$time_to_solve)

edited Dec 01 '11 at 16:15

Pops

30,199
37
136
151

answered Nov 30 '11 at 22:26

Martin.

10,494
3
42
68

score 2 · Answer 2 · answered Dec 01 '11 at 07:03

You can use a SEC_TO_TIME function. Take a look at this script -

CREATE TABLE table_time(
  column1 TIME DEFAULT NULL
);

INSERT INTO table_time VALUES(SEC_TO_TIME(3600));

SELECT * FROM table_time;
+----------+
| column1  |
+----------+
| 01:00:00 |
+----------+

Inserting seconds into a database in HH:MM:SS format

2 Answers2