Page 1 of 1

Store dates after March 1, 2038

Posted: Wed Dec 05, 2007 1:09 pm
by bswingin
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Can anyone tell me how I store in my MySQL database table dates after the UNIX epoch - March 2038?  The data type I am using for this column of data is DATETIME.  I have a for loop that sends future dates to the database and when the date goes higher than March 1, 2038 the timestamp becomes negative and stores the date December 31, 1969.  Is there any way around this so I can store dates higher than March 1, 2038?  Here is the code:

Code: Select all

for($j=0;$j<$timeLeft;$j++){
					
		$annualIncomeDt = mktime( 0, 0, 0, $thisMonth, $thisDate, $thisYear );
		$annualIncomeDt = date( "Y-m-d", $annualIncomeDt );
				
		$annualIncomeInsertArray[$k] = "INSERT INTO T_Budget_Plan (Action_ID,Homeowner_ID,Action_Date,Action_Type,Action_From,Action_To,Action_Deposit,Action_Executed,Source_ID) VALUES (NULL," . $this->homeOwnerID . ",'" . $annualIncomeDt . "','Income','" . $annualIncomeName . "','ICA'," . $annualIncomeAmount . ",'n'," . $incomeSourceId . ");";
						
		mysqli_query( $mysqli, $annualIncomeInsertArray[$k] );
						
		$thisAction = mysqli_insert_id( $mysqli );
						
		$this->setBudgetPlanBal( $annualIncomeDt, $thisAction );
		$this->updateFutureBPlanBals( $annualIncomeDt, $thisAction );
						
		$thisYear++;
		$k++; 
	}//End insert stmts FOR loop

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Wed Dec 05, 2007 1:22 pm
by feyd
Don't use a unix time-stamp as it will not work beyond that point right now.

Not using UNIX timestamp

Posted: Wed Dec 05, 2007 1:42 pm
by bswingin
Feyd, thanks for your help. I am new at this, so I was wondering if you could tell me how to do it otherwise. I only know how to use UNIX timestamps. Thanks again!

Posted: Wed Dec 05, 2007 2:27 pm
by feyd
You'll have to do the math yourself at some level, or look for a time/date class which doesn't rely on unix time-stamps to shift and set it's value.

I posted a class that handles very large (and very small) time and date values a while ago, but I don't recall exactly how it handled not using time-stamps. I do remember it used modified Julian dates to store the value.

The database you are using supports dates beyond 2038 as well.

viewtopic.php?t=65853

Posted: Wed Dec 05, 2007 2:32 pm
by John Cartwright
Heres a good place to start for Mysql's Datetime Functions