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.

[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. :)

[TSM]

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.

[Grinch]

Huh?

[Baph]

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

[Baph]

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

[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);

[Baph]

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

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.

[TSM]

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

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.

I dont want to know the last weeks worth of data, i have used the INTERVAL stuff elsewhere when i dont know what the period start date is.

I need to know the start date of the current week cycle based on the start period date, that start period date could be set 2 years ago and its any day within the week, so i need to keep counting 7 days until im within this weeks billing cycle.

[TSM]

I could probably do the calculation in MySQL but its much harder, ile just use PHP to get the date then push that into the mysql querystring

[Baph]

I dont want to know the last weeks worth of data, i have used the INTERVAL stuff elsewhere when i dont know what the period start date is.

I need to know the start date of the current week cycle based on the start period date, that start period date could be set 2 years ago and its any day within the week, so i need to keep counting 7 days until im within this weeks billing cycle.

Ah, see, that's different to...

... 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.

Clarity of specification. ;)

If the spec is "Given any random date, and given an interval of time, calculate the start and end of that period." Then you'd need to know, or assume some form of "epoch" for the project, and as you say, count from the epoch until the given date (counting in the given interval). The previous iteration would be the start, and the next iteration would be the end of the range required.

Unfortunately, there's no real way around that. At least, not in PHP.

[timwilky]

I recently had problems with date in queries suddenly failing. worked fine on my dev systems but bounced on the deploy system, turns out a sys admin type had changed the locale and suddenly the SQL was not a properly formatted date.

Will remember to check locales in future, it took me 3 days of debugging and head scratching to work that out

[TSM]

Ah, see, that's different to...



Clarity of specification. ;)

If the spec is "Given any random date, and given an interval of time, calculate the start and end of that period." Then you'd need to know, or assume some form of "epoch" for the project, and as you say, count from the epoch until the given date (counting in the given interval). The previous iteration would be the start, and the next iteration would be the end of the range required.

Unfortunately, there's no real way around that. At least, not in PHP.

I know its a bummer, but ive got weekly, monthly, yearly, daily sorted out, quartly seems easy as its just montly with a 3 multiplier, but being picky about it i need to add extra code to work out if the epoch loops have advanced over feb/sept or a leap year and resync the date.

As this is not a critical thing i may just leave it as its been done now.

[Baph]

I know its a bummer, but ive got weekly, monthly, yearly, daily sorted out, quartly seems easy as its just montly with a 3 multiplier, but being picky about it i need to add extra code to work out if the epoch loops have advanced over feb/sept or a leap year and resync the date.

As this is not a critical thing i may just leave it as its been done now.

If all you really care about is the periods, you can use the above code you posted, with a minor tweak.

1. Obtain "epoch" in milliseconds.
2. Determine the required interval (weeks/months/quarters/years etc), and calculate number of milliseconds per interval.
3. Determine number of milliseconds since "epoch" for the given date.
4. Continually loop, adding #2 to #1 until reaching/exceeding #3.
5. Determine start/end of period in milliseconds.
6. Convert two values from #5 using the PHP function date().

The above really won't care about leap years etc, as this is catered for by date() for you (all calculations are done in ms first). You could even then create a function to call, whereby you can pass the interval (#2 above) to allow you to re-use the code regardless of interval. :)