Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Get timestamps for a week timespan

Status
Not open for further replies.

spamjim

Instructor
Mar 17, 2008
1,368
US
I'm trying to generate some reports that show events that occur between 12:00:00 am Sunday morning and 11:59:59 Saturday night. My problem is calculating the unix timestamp range. Can anyone offer a genius idea? I'm at a mental block.

To assist the user in choosing the date range, I'd like PHP to generate a HTML select form field that has options like this...

10/19/2008 - 10/25/2008 (value = 1224374400,1224979199 )
10/26/2008 - 11/01/2008 (value = 1224979200,1225583999 )
...

What's the best way to calculate Sunday morning/Saturday night timestamps in a 52 step loop for a year?
 
A timestamp is a decimal showing a number of seconds.
So to add 1 week to it add (60 * 60 * 24 * 7) seconds

60 secs = 1 min
* 60 mins = 1 hour
* 24 hrs = 1 day
* 7 = 1 week

--
Tek-Tips Forums is Member Supported. Click Here to donate

<honk>*:O)</honk>

Tyres: Mine's a pint of the black stuff.
Mike: You can't drink a pint of Bovril.


 
Sorry, I misunderstood your question :)

--
Tek-Tips Forums is Member Supported. Click Here to donate

<honk>*:O)</honk>

Tyres: Mine's a pint of the black stuff.
Mike: You can't drink a pint of Bovril.


 
I would not do things quite the way that you suggest spamjim. can you share you sql schema for the relevant database table?

my suggestion would be to use the week() and year() functions in your query (assuming you are using mysql). Function reference is here. If these functions are not available to you, or you are set on your request, then i will post how to achieve the aim your way.
 
I think I got it worked out. It is messy but it gets the job done.

I basically just have a MySQL table with each row having a timestamp (and other junk). The selection below will pass off a high and low value to a query to find the database rows that fall in the range.

Code:
echo '<select name="weeks"><option selected="selected" value="">Choose week...</option>';

$now = date("Y/m/d");
$dt = explode("/", "$now");
$dt = strtotime( $dt[0]."/".$dt[1]."/".$dt[2] );
$lastsun = strtotime( "last Sunday", $dt );
$nextsat = $lastsun + 604799;
echo '<option value="' . $lastsun . '|' . $nextsat . '">';
echo date( "m/d/Y", $lastsun );
echo ' - ';
echo date( "m/d/Y", $nextsat );
echo '</option>';
	 
for ($i = 52; $i >= 1; $i--) {
$lastsun = strtotime( "last Sunday", $lastsun );
$nextsat = $lastsun + 604799;
echo '<option value="' . $lastsun . '|' . $nextsat . '">';
echo date( "m/d/Y", $lastsun );
echo ' - ';
echo date( "m/d/Y", $nextsat );
echo '</option>';
if ($nextsat <= '1222819200') { $i = 0; }
}

echo '</select>';
 
when you say timestamp, from a mysql perspective are you using an integer column with a unix timestamp within, or a mysql timestamp in the form YYYY-MM-DD [HH:MM:SS]?

i think you may be overcomplicating slightly, but if it works for you: Great!
 
Unix timestamps are used.

The script above is the result of working too late into the night. I might as well have been drinking. [bigsmile]
 
this might work for you then. not sure whether it's any better than yours though!.


Code:
function makeSelect( $year = null){
	$year = (empty($year)) ? date('Y') : $year;
	$firstDay = strtotime($year .'-01-01 00:00:00');
	$sunday = (date('w', $firstDay) === 0) ? $firstDay : strtotime('next Sunday', $firstDay);
	$select = '<select name="date">' . "\r\n";
	whilst (date('Y', $sunday) == $year){	//stay within the year
		$display = date('d/m/Y', $sunday) .' - ' . date ('d/m/Y', strtotime('next Saturday', $sunday);
		$select .= "\t<option value=\"$sunday\">$display</option>\r\n";
		$sunday = strtotime('+1 week', $sunday);
	}
	$select .= "</select>";
	return $select;
}
// pass the incoming field value from the select box and the name of your field in the table
function getwhereclause($date, $fieldName){
	return " $fieldName BETWEEN $date AND " . strtotime('+6 days 23 hours 59 minutes 59 seconds', $date);
}

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top