?
Solved

MySQL vs PHP date/time timestamp conflict

Posted on 2005-04-13
9
Medium Priority
?
380 Views
Last Modified: 2013-12-12
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?
0
Comment
Question by:DrDamnit
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 6

Accepted Solution

by:
peyox earned 2000 total points
ID: 13772641
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
 
LVL 32

Author Comment

by:DrDamnit
ID: 13772779
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
 
LVL 1

Expert Comment

by:Klodorff
ID: 13772816
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:Klodorff
ID: 13772872
Also check out: http://php.net/manual/ref.datetime.php
-for transforming datestamps and such
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 13772995
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
 
LVL 6

Expert Comment

by:peyox
ID: 13774030
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
 
LVL 9

Expert Comment

by:AlanJDM
ID: 13775739
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
 
LVL 32

Author Comment

by:DrDamnit
ID: 13785637
Does MySQL have a function to convert the UNIX_TIMESTAMP() returns back into MySQL timestamp format?
0
 
LVL 6

Expert Comment

by:peyox
ID: 13786351
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days socially coordinated efforts have turned into a critical requirement for enterprises.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question