MySQL vs PHP date/time timestamp conflict

I have an application where each user is assigned an expiration date. If the expiration date (a MySQL datetime type) is empty (0000-00-00 00:00:00) then the user does not expire. If there is a date present, I want the user to expire on and after that date.

Question:
MySQL uses one date/time format, and PHP uses another. What's the best way to handle dates?
LVL 32
DrDamnitAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
peyoxCommented:
When you want to display a date from MySQL you should use UNIX_TIMESTAMP() function. It will return a timestamp in the same format as php date() uses.

Example:
select UNIX_TIMESTAMP(datecolumn) from table

other way (a php way) is to parse date returned from MySQL with strtotime() function.

Personally I would sugest the first solution.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
DrDamnitAuthor Commented:
How about going form user input to MySQL datetime format? For instance, if in a text box, I type 12/10/1979, is there a function that will translate that date?
0
 
KlodorffCommented:
Hi there Doctor!

When I'm playing with dates with MySQL and PHP I use default TIMESTAMP-format in MySQL (0000-00-00 00:00:00) and creates my PHP-scripts working with the same format (date(Y-m-d H:i:s)).

So if you want a user to expire after a certain date, simply make a script that executes daily that looks like this:

<?
//Connect to MySQL

$query = mysql_query("SELECT username,exp_date FROM users WHERE active='1'")or die("Error. Query#1: ".mysql_error());
$today = date("Y-m-d");

while($res = mysql_fetch_array($query)){
  /*Take away the hours. Only first 10 digits will be compared (0000-00-00).*/
  $user_exp_date=substr($res['exp_date'], 0, 10);
 
 if($user_exp_date <= $today && $user_exp_date != '0000-00-00'){
  /*In-activate user if date expired.*/  
  mysql_query("UPDATE users SET active='0' WHERE username='".$res[username]."'")or die("Error. Query#2:   ".mysql_error());
 }else{
  /*User have time left until in-activation, and can be left without adjustments..*/
 }
}

?>

Hope I gave you what you wanted.
/Klodorff
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
KlodorffCommented:
Also check out: http://php.net/manual/ref.datetime.php
-for transforming datestamps and such
0
 
DrDamnitAuthor Commented:
How would I use the UNIX_TIMESTAMP in the following query:

$sql = "SELECT * FROM users LEFT JOIN permissions ON users.users_id=permissions.users_id LEFT JOIN modules on modules.modules_id = permissions.modules_id  WHERE modules.modules_page = '$page'";
0
 
peyoxCommented:
SELECT UNIX_TIMESTAMP(datecolumn), * FROM users LEFT JOIN permissions ON users.users_id=permissions.users_id LEFT JOIN modules on modules.modules_id = permissions.modules_id  WHERE modules.modules_page = '$page'";

I don't know your column names so I put 'datecolumn' instead.
0
 
AlanJDMCommented:
Generally speaking, the combination of the date() function and/or the strtotime() function can do any date manipulation needed to pass dates of almost any format back and forth between PHP and MySQL. See the examples on the following pages from the PHP manual...

http://us4.php.net/manual/en/function.date.php
http://us4.php.net/manual/en/function.strtotime.php


Alan
0
 
DrDamnitAuthor Commented:
Does MySQL have a function to convert the UNIX_TIMESTAMP() returns back into MySQL timestamp format?
0
 
peyoxCommented:
Yes, it has such a function: FROM_UNIXTIME(unix_timestamp)
ie:
select FROM_UNIXTIME(date_from_php)

from mysql.com:
Returns a representation of the unix_timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context.

SELECT FROM_UNIXTIME(875996580);        
   -> '1997-10-04 22:23:00'
SELECT FROM_UNIXTIME(875996580) + 0;        
   -> 19971004222300
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.