PHP / MySQL coders out there, script help

[TSM]

Ive got to code up a tricky date calculating script, i dont do much with dates.

Ive got to calculate the folowing...

Things i know, range type ie.. weekly/monthly/quart/yearly, i also have a period start date that all thoes ranges need to be keyed against which is based against the user.

So ie if user has range setup as monthly i use the folowing calculation to work out the start date, it accounts for leap years and also feb/sep day diffrences. The year in the periodstart date is not used in these instances realy.

$periodstart = "2007-05-04 00:00:00"
$limdate = strtotime($periodstart);
if(date("d",$limdate)>=date("d")){$month_offset=1;}else{$month_offset=0;}
if(date("d",$limdate)>=date("d") && date("m")==1){$month=12;$year=date("Y")-1;}else{$month=date("m")-$month_offset;$year=date("Y");}
$day = date("d",$limdate);
if($month == 2 && $day > 28 && date("L")==0){$day=28;}
elseif($month == 2 && $day > 28 && date("L")==1){$day=29;}
elseif($month == 9 && $day > 30){$day=30;}
$date = mktime(0,0,0,$month,$day,$year);
$where = "time >= \"".date('Y-m-d',$date)."\"";

// Date has been calculated as 2009-04-04

This is very crude but it seems to work, any ideas if this can be made easier.

I also need to do this for the quartly and weekly scripts

Weekly
So where i have ...
$periodstart = "2007-05-04 00:00:00"
the final date i would need to get is 2009-04-27

Usualy this is all easy if i was to stay within the same year but as it needs to take into account that if it crosses a year boundary. Its also easy if i dont need to key it to a date, but in this instance i need to use the start dates.

Im sure there must be an easier way or im looking at it all wrong.

Any help would be brill.

TSM, far far far too over complicated....

Drop to a MySQL console, and play with the following...

$ndbconn = mysql_connect("localhost", "user", "password"); 
mysql_select_db("mysql"); 

$n_start_date = 1062521254; // Timestamp for 2003-09-02 11:47:34 taken from your DB 

// The query adds fourteen days to the given date 
$squery = "SELECT DATE_ADD(FROM_UNIXTIME($n_start_date), INTERVAL 14 DAY)"; 
$nresult = mysql_query($squery); 
$s_new_date = mysql_result($nresult, 0, 0); 

// Free the result 
mysql_free_result($nresult); 

// Disconnected 
mysql_close($ndbconn); 
echo "Your password will expire on $s_new_date";

Note the $squery variable.

[TSM]

Originally Posted by Baph

TSM, far far far too over complicated....

Drop to a MySQL console, and play with the following...

$ndbconn = mysql_connect("localhost", "user", "password"); 
mysql_select_db("mysql"); 

$n_start_date = 1062521254; // Timestamp for 2003-09-02 11:47:34 taken from your DB 

// The query adds fourteen days to the given date 
$squery = "SELECT DATE_ADD(FROM_UNIXTIME($n_start_date), INTERVAL 14 DAY)"; 
$nresult = mysql_query($squery); 
$s_new_date = mysql_result($nresult, 0, 0); 

// Free the result 
mysql_free_result($nresult); 

// Disconnected 
mysql_close($ndbconn); 
echo "Your password will expire on $s_new_date";

Note the $squery variable.

Ahh i thought that route before but it does not work.
This is not for password usage, its for finding all records in a table where they are within the period to now.

Ie i have a table of log entries that has a list of image downloads that the client has done, client X has a limit of 50 images per month/week/quarter/year but the start of each period has to be keyed to their periodstart date, ie startperiod is 2009-01-31, if user had yearly limits on then the start date would be 2008-01-31, if it was monthly it would be 2008-12-31, if it was weekly then it would be 2009-01-24.

Huh?

Originally Posted by TSM

Ie i have a table of log entries that has a list of image downloads that the client has done, client X has a limit of 50 images per month/week/quarter/year but the start of each period has to be keyed to their periodstart date, ie startperiod is 2009-01-31, if user had yearly limits on then the start date would be 2008-01-31, if it was monthly it would be 2008-12-31, if it was weekly then it would be 2009-01-24.

SELECT DATE_ADD(FROM_UNIXTIME($n_start_date), INTERVAL 1 YEAR);

That would return the date 1 year from $n_start_date (in Unix timestamp format).

SELECT DATE_ADD(FROM_UNIXTIME($n_start_date), INTERVAL 1 MONTH);

That would return the date 1 month from $n_start_date (again, in Unix timestamp).

My original post, whilst a little obscure, was just really saying to use the MySQL Date_Add() function to calculate date differences. Take a look at the MySQL documentation for DATE_ADD() - it'll calculate everything much easier for you.

EDIT: MySQL DATE_SUB() for subtractions.

If you want, it could even be built into a nested statement and all executed within the DB to return the number of images per period, then use mysql_num_rows() to see if they're over the limit.

[timwilky]

I will be honest with you, I wrote a java date class to give me date info such as start/end of week, month, year etc as system times for one of my projects.




package com.alstom.stats;

/**
* Insert the type's description here.
* Creation date: (10/01/2003 11:18:37)
* @author: Administrator
*/
import java.util.*;
import java.text.*;
public class GetMonthStartandEnd {
/**
* GetMonthStartandEnd constructor comment.
*/
public GetMonthStartandEnd() {
super();
}
/**
* Insert the method's description here.
* Creation date: (10/01/2003 11:26:25)
* @return long
* @param date long system time in seconds
*
*/
public static long getEndofCurrentYear() {

Date startDate = new Date();

Calendar calendar = new GregorianCalendar();
calendar.setTime(startDate);
int month = calendar.get(calendar.MONTH);
calendar.set(Calendar.MILLISECOND, 999);
calendar.set(Calendar.SECOND, 59);
calendar.set(Calendar.MINUTE, 59);
calendar.set(Calendar.HOUR_OF_DAY, 23);
calendar.set(Calendar.DATE, 31);
calendar.set(Calendar.MONTH, Calendar.DECEMBER);


long eoy = calendar.getTime().getTime();
return eoy;
}
/**
* Insert the method's description here.
* Creation date: (10/01/2003 11:26:25)
* @return long
* @param month int
* @param year int
*/
public static long getEndofMonth(int month, int year) {
Calendar calendar = new GregorianCalendar();
calendar.set(year,month-1,1,0,0);
calendar.set(Calendar.MILLISECOND,0);
calendar.set(Calendar.SECOND,0);
calendar.set(Calendar.MINUTE,0);
calendar.add(Calendar.MONTH,1);
long som=calendar.getTime().getTime();
som--;


return som;
}
public static long getEndofYear( int year) {
Calendar calendar = new GregorianCalendar();
calendar.set(year,Calendar.DECEMBER,31,23,59);
calendar.set(Calendar.MILLISECOND,999);
calendar.set(Calendar.SECOND,59);
long som=calendar.getTime().getTime();
return som;
}
public static long getStartofYear( int year) {
Calendar calendar = new GregorianCalendar();
calendar.set(year,Calendar.JANUARY,1,0,50);
calendar.set(Calendar.MILLISECOND,0);
calendar.set(Calendar.SECOND,0);
long som=calendar.getTime().getTime();
return som;
}
/**
* Insert the method's description here.
* Creation date: (10/01/2003 11:26:25)
* @return long
* @param month int
* @param year int
*/
public static long getEndofMonth(long date) {

Date startDate = new Date(date*1000);

Calendar calendar = new GregorianCalendar();
calendar.setTime(startDate);
calendar.set(Calendar.MILLISECOND,0);
calendar.set(Calendar.SECOND,0);
calendar.set(Calendar.MINUTE,0);
calendar.set(Calendar.HOUR_OF_DAY,0);
calendar.set(Calendar.DATE,1);
calendar.add(Calendar.MONTH,1);
long som=calendar.getTime().getTime();
som--;



return som;
}
/**
* Insert the method's description here.
* Creation date: (10/01/2003 11:26:25)
* @return long
* @param date long system time in seconds
*
*/
public static int getMonth(long date) {

Date startDate = new Date(date*1000);

Calendar calendar = new GregorianCalendar();
calendar.setTime(startDate);







int month =calendar.get(calendar.MONTH);
return month;
}
/**
* Insert the method's description here.
* Creation date: (10/01/2003 11:26:25)
* @return long
* @param date long system time in seconds
*
*/
public static long getNow() {

Date now = new Date();

Calendar calendar = new GregorianCalendar();
calendar.setTime(now);







long som=calendar.getTime().getTime();



return som;
}
/**
* Insert the method's description here.
* Creation date: (10/01/2003 11:26:25)
* @return long
* @param date long system time in seconds
*
*/
public static long getStartofCurrentFinYear() {

Date startDate = new Date();

Calendar calendar = new GregorianCalendar();
calendar.setTime(startDate);
int month = calendar.get(calendar.MONTH);
calendar.set(Calendar.MILLISECOND, 0);
calendar.set(Calendar.SECOND, 0);
calendar.set(Calendar.MINUTE, 0);
calendar.set(Calendar.HOUR_OF_DAY, 0);
calendar.set(Calendar.DATE, 1);
calendar.set(Calendar.MONTH, Calendar.APRIL);
if (month < Calendar.APRIL)
calendar.add(Calendar.YEAR, -1);

long som = calendar.getTime().getTime();
return som;
}
/**
* Insert the method's description here.
* Creation date: (10/01/2003 11:26:25)
* @return long
* @param date long system time in seconds
*
*/
public static long getStartofCurrentYear() {

Date startDate = new Date();

Calendar calendar = new GregorianCalendar();
calendar.setTime(startDate);
int month = calendar.get(calendar.MONTH);
calendar.set(Calendar.MILLISECOND, 0);
calendar.set(Calendar.SECOND, 0);
calendar.set(Calendar.MINUTE, 0);
calendar.set(Calendar.HOUR_OF_DAY, 0);
calendar.set(Calendar.DATE, 1);
calendar.set(Calendar.MONTH, Calendar.JANUARY);
long som = calendar.getTime().getTime();
return som;
}
/**
* Insert the method's description here.
* Creation date: (10/01/2003 11:26:25)
* @return long
* @param date long system time in seconds
*
*/
public static long getStartofFinYear(long date) {

Date startDate = new Date(date*1000);
int month = getMonth(date);

Calendar calendar = new GregorianCalendar();
calendar.setTime(startDate);
calendar.set(Calendar.MILLISECOND,0);
calendar.set(Calendar.SECOND,0);
calendar.set(Calendar.MINUTE,0);
calendar.set(Calendar.HOUR_OF_DAY,0);
calendar.set(Calendar.DATE,1);
calendar.set(Calendar.MONTH,Calendar.APRIL);
if (month < Calendar.APRIL)
calendar.add(Calendar.YEAR, -1);





long som=calendar.getTime().getTime();
return som;
}
/**
* Insert the method's description here.
* Creation date: (10/01/2003 11:26:25)
* @return long
* @param month int
* @param year int
*/
public static long getStartofMonth(int month, int year) {
Calendar calendar = new GregorianCalendar();
calendar.set(year,month-1,1,0,0);
calendar.set(Calendar.MILLISECOND,0);
calendar.set(Calendar.SECOND,0);
calendar.set(Calendar.MINUTE,0);

long som=calendar.getTime().getTime();


return som;
}
/**
* Insert the method's description here.
* Creation date: (10/01/2003 11:26:25)
* @return long
* @param date long system time in seconds
*
*/
public static long getStartofMonth(long date) {

Date startDate = new Date(date*1000);

Calendar calendar = new GregorianCalendar();
calendar.setTime(startDate);
calendar.set(Calendar.MILLISECOND,0);
calendar.set(Calendar.SECOND,0);
calendar.set(Calendar.MINUTE,0);
calendar.set(Calendar.HOUR_OF_DAY,0);
calendar.set(Calendar.DATE,1);




long som=calendar.getTime().getTime();
return som;
}
/**
* Starts the application.
* @param args an array of command-line arguments
*/
public static void main(java.lang.String[] args) {
// Insert code to start the application here.
//expect 2 arguments month and year
int month =-1, year=-1;
if(args.length != 2){
System.out.println("Usage: GetMonthStartandEnd month year");
return;
}

try{
month = Integer.parseInt(args[0]);
year = Integer.parseInt(args[1]);
long start = getStartofMonth(month,year);
long end = getEndofMonth(month,year);
System.out.println("month start = "+ start + " : End = " + end);
Date startDate = new Date(start);
Date endDate = new Date(end);
Calendar calendar = new GregorianCalendar();
calendar.setTime(startDate);
DateFormat dt = DateFormat.getTimeInstance();
DateFormat df = DateFormat.getDateInstance();
System.out.println("month start = " +df .format(calendar.getTime()) + " " + dt .format(calendar.getTime()));
calendar.setTime(endDate);
System.out.println("month end = " +df.format(calendar.getTime()) + " " + dt .format(calendar.getTime()));
end = getStartofDay(end/1000);
endDate = new Date(end);
calendar.setTime(endDate);
System.out.println("start of last day = " +df.format(calendar.getTime()) + " " + dt .format(calendar.getTime()));
end = getStartofDay(end/1000);
endDate = new Date(end + 3600000);
calendar.setTime(endDate);
System.out.println("start of last day + 3600 = " +df.format(calendar.getTime()) + " " + dt .format(calendar.getTime()));



}
catch (NumberFormatException e){
e.printStackTrace();
}



}

/**
* Insert the method's description here.
* Creation date: (16/06/2005 13:41:22)
* @return long
* @param date long
*/
public static long getStartofDay(long date) {

Date startDate = new Date(date*1000);

Calendar calendar = new GregorianCalendar();
calendar.setTime(startDate);
calendar.set(Calendar.MILLISECOND,0);
calendar.set(Calendar.SECOND,0);
calendar.set(Calendar.MINUTE,0);
calendar.set(Calendar.HOUR_OF_DAY,0);
long som=calendar.getTime().getTime();



return som;
}
}


probably of no use, but I called these from a php system call where necessary to a wrapper class

Tim, that doesn't really help much does it?

[timwilky]

I was trying to demonstrate there are multiple ways of dealing with dates and being able to construct /calculate time.

PHP uses functions such as date_add, dateinterval etc to calculate dates/ arithmetic, offsets etc

[TSM]

Code to work out the latest weekly date cycle given an initial date.
Smple but seems to work

$date = strtotime("2008-05-5");
$date_now = strtotime("2009-05-7");
$date_now_epoch = date("U",$date_now);
$date_epoch=date("U",$date);

$new_epoch_date=0;
$cur_epoch_date=$date_epoch;
while($cur_epoch_date <= $date_now_epoch){
  $new_epoch_date=$cur_epoch_date;
  $cur_epoch_date=$cur_epoch_date+(86400*7);
}
echo date("Y-m-d",$new_epoch_date);

I still can't fathom why you don't let the DB do the work for you in this scenario.

If all you're after is the number of records within a given date range from today (or any arbitary date), going backwards...

<?php
 
// subsitute these vars for whatever you need
$table = "whatever";
$date_col = "date_created";
$interval = "WEEK"; // or MONTH or YEAR etc
$int_count = 1;
$cur_date = '08-05-09';
 
$query = "SELECT * FROM $table WHERE $date_col <= $cur_date AND $date_col >= DATE_SUB($cur_date, INTERVAL $int_count $interval) ORDER BY $date_col DESC;"
 
$result = mysql_query($query) or die(mysql_error());
 
// you now have a MySQL result resource containing all records from $table within the last week, ordered by the date column (newest first).
?>

Obviously sanitize variables to prevent injection etc if they are user editable.