CODEWITHSUNDEEP
phpmysql
Code Lover
Code Lover

Reputation: 8348

Inserting actual hours (not time) to MySQL

I am trying to insert actual hours not the time itself to MySQL database through form fields. So for example

$time1 = '00:00';
$time2 = '27:20';
$time3 = '00:45';

So I can retrieve the different rows and can calculate on the fly whenever require. Either through search query or even in other area of the system.

When I have tried to do addition of above three times, it is not giving the result the way I am looking for

$total = strtotime($time1) + strtotime($time2) + strtotime($time3);
echo date('H:i:s', $total);

The result

14:16:44

While it should be something like

28:05:00

I have used TIME DATATYPE in MySQL table. I may use as a TEXT but I am also concern about the error happen in user input. Where I do not have to force the user to insert the any particular format but they can either insert as below way

27.20
27:20

or

1.5
1:30

My main concern is to calculate the time, the user input can be on second priority but it would be great if can implement.

So is there anyway, idea or hint to achieve this?

Upvotes: 0

Views: 62

Answers (2)

Evert
Evert

Reputation: 99523

If you're only interested in the hours and minutes, why don't you just store the value as an in integer? Just multiply the hours by 60.

You can handle the conversion in PHP.

Alternatively, you can also easily use two (very small) int fields for this.

Upvotes: 0

outlyer
outlyer

Reputation: 3943

date() expects the timestamp in UNIX format, i.e. seconds since January 1 1970 00:00:00 UTC (which is also the value provided by strtotime)

You're passing it the result of adding a series of amounts of time since 1 January 1970 instead of just adding up hours, so (as far as date is concerned) you're generating a random date and time, and printing only the time (try printing the date of $total and see what you get).

Since your time is stored in the database, one possibility is to let MySQL handle the time calculations itself, e.g.:

SELECT ADDTIME('00:00',ADDTIME('27:20','00:45'))

will result in "28:05:00". You can have your database fields as TIME and operate on them directly through SQL, and do the user input conversions into acceptable TIME values in PHP.

Upvotes: 1

Related Questions