converting time

[TravisB]

I am working on a time sheet program. The user selects the date, hour, and minute they started and stopped working. I am trying to convert and store the times as seconds since epoch.

I can convert the date selected with no problems. What I am having trouble with is taking the hours amd minutes selected from the drop-down and converting them into seconds to add the date which has already been converted.

Here is a sample from the form:

<select name="start_hour">
	<option value="01">01</option>
	<option value="02">02</option>
	<option value="03">03</option>
	<option value="04">04</option>
	<option value="05">05</option>
	<option value="06">06</option>
	<option value="07">07</option>
	<option value="08">08</option>
	<option value="09">09</option>	
	<option value="10">10</option>
	<option value="11">11</option>	
	<option value="12">12</option>
</select>
<select name="start_minutes">
	<option value="00">00</option>
	<option value="15">15</option>
	<option value="30">30</option>
	<option value="45">45</option>
</select>
<select name="start_ampm">
	<option value="AM">AM</option>
	<option value="PM">PM</option>
</select>

<!-- date selected -->
<input type="hidden" name="lt_day" value="2005-11-03">

And here is part of the PHP

$starttime = strtotime($lt_day); //equals 1130994000

I have been reading the date and time functions but keep getting confused. How can I take the time selected in the form and add it to $starttime?

[devinemke]

$lt_day = '2005-11-03';
$start_hour = '03';
$start_minutes = '15';
$start_ampm = 'PM';

$starttime = strtotime($lt_day . ' ' . $start_hour . ':' .  $start_minutes . ' ' . $start_ampm);

[TravisB]

Thanks for the advice. This is my code:

$start_string = $lt_day . ' ' . $start_hour . ':' . $start_minutes . ':00 ' . $start_ampm;
$start_string_time = strtotime($start_string);
echo "<br>start_string_time = " . $start_string_time;
//echoes start_string_time = 1131093900

I do have another question. Now that I have a date stored in seconds, ex. 1131093900, can I search for it using an sql statement?

Say I want to find dates that equal 2005-11-05, can I convert data stored in the database like 1131093900 on the fly and compare it?

[devinemke]

TravisB wrote:

Now that I have a date stored in seconds, ex. 1131093900, can I search for it using an sql statement?

Say I want to find dates that equal 2005-11-05, can I convert data stored in the database like 1131093900 on the fly and compare it?

yes. it all depends on how you are storing your dates. if you are storing dates as unix timestamps (integers) then there is no conversion needed. if you are using the native DATE type (like mySQL's DATE YYYY-MM-DD) then you can use any number of mySQL's Date and Time Functions to compare/add/subtract dates.

[TravisB]

I am storing the data in seconds since epoch, like 1131093900, in the database. However, my form sends the date like 2005-11-5.

I want my sql code to take the date from the form, look at the dates and times in the database and retreive matches.

Maybe the best way would be to
1. convert 2005-11-5 to seconds
2. take that time and add 86,400 (24h 60m 60s)
3. search for times between the numbers in steps 1 and 2

I will try that.

Please let me know if there is a more efficient process.

Thanks,
Travis

[devinemke]

TravisB wrote:

I am storing the data in seconds since epoch, like 1131093900, in the database. However, my form sends the date like 2005-11-5.

then just convert the form data to timestamps using [man]strtotime[/man] and then do something like:

SELECT * FROM table WHERE date BETWEEN $start_timestamp AND $end_timestamp