[RESOLVED] PHP / mySQL time question.

@nasToneDec 09.2007

I have written a simply vb.net application that will store and display data from my mySQL net database. I also have on online form that allows users to input data into this database as well. When I enter data from the vb.net program it is formatted as follows:

CREATE_DATE CREATE_TIME
12/8/2007 12:02 PM

When a user input information from the web form, it comes into the database formatted like this:

CREATE_DATE CREATE_TIME
2007/12/08 13:02:32

It seems as if the time being input from the web is an hour off of my actual time. I am assuming it is a timezone issue. Is there a way to remedy this? I am pretty new to PHP. Here is code I am using to do this:

[code=php] <?php } else { $user_name = $_POST[‘user_name’]; $password = $_POST[‘password’]; $create_date = date(‘Y/m/d’); $create_time = date(‘H:i:s’); mysql_query(“INSERT INTO `Valid_User` (user_name, password, create_date, create_time) VALUES (‘$user_name’, ‘$password’, ‘$create_date’, ‘$create_time’)”); echo “Your request has been added!”; echo ‘<meta http-equiv=”refresh” content=”5;url=http://customdesignsource.com/Auth.php”>’; } ?> [/code]

Formatting the date is a simple fix – I can take care of that. I did a little bit of searching on the “time offset” issue, but I can’t find anything.

Any help would be appreciated!

to post a comment

PHP

8 Comments(s) _↴

@holidayDec 09.2007 — #well I would highly recommend using the DATETIME type for the field in the database not a separate date and time field, using date time makes it easier to order results by date and it allows you make make a query of WHERE dateTime>'2007-12-09 18:03:54', which can come in handy.

Here is the DATETIME format: 2007-12-09 18:03:54 (yyyy-mm-dd hh:mm:ss), and don't forget to use leading zeros "09" NOT "9".

here is the php code to generate the current date time [code=php]$dateTime = date("Y-m-d H:i:s");[/code]

Now to deal with your wrong time issue. The php "date()" function outputs date and time based on the servers time. This is normally a problem, because there is a good chance you won't be in the same time zone as your host! And even if you are, or if you write your scripts to offset the time based on you server, what if you change hosts, and the new host has a different time zone, then everything will be screwed up! I recently ran into a similar issue with one of my websites, and you don't want to make the same mistake. Always save all of your dates and times under GMT (Greenwich Mean Time) time zone/UTC (Universal Time Coordinated), (GMT and UTC are the same). To accomplish the feat, all you have to do is use the "gmdate()" function instead of the "date()" function. So the php code would be [code=php]$dateTime = gmdate("Y-m-d H:i:s");[/code] Now we're still not done. Odds are your time zone isn't GMT time, so now we need a way to convert GMT time to your time Zone. Here are three handy functions that, put together do just that:

[code=php]
 //Create Function: convert unix time stamp to dateTime
 function tS_to_dT($ts) {
 //return dateTime form time stamp
 return gmdate("Y-m-d H:i:s", $ts);
 }
 
 //Create Function: convert date time to unix time stamp
 function date_time_to_unix_ts($dateTime){
 //separate date from time
 $exp = explode(" ", $dateTime);
 $date = $exp[0];
 $time = $exp[1];
 
  //if no time make zeros
  if($time=='') {
   $time = '00:00:00';
  }
 
  //separate year, month and day
  $exp = explode("-", $date);
  $year = $exp[0];
  $month = $exp[1];
  $day = $exp[2];
 
  //separate hour, minute, and second
  $exp = explode(":", $time);
  $hour = intval($exp[0]);
  $minute = intval($exp[1]);
  $second = intval($exp[2]);
 
  //return Unix time stamp
  return gmmktime($hour, $minute, $second, $month, $day, $year);
 }
 
 //Create Function: offet GMT time
 function gmt_dt_to_client_dt($dateTime, $offSet) {
 
  //convert dateTime to unix time stamp
  $timeStamp = date_time_to_unix_ts($dateTime);
 
  //add offset to Time Stamp
  $timeStamp += ($offSet*3600);
 
  //change unix time stamp to dateTime
  $dateTime = tS_to_dT($timeStamp);
 
  //return dateTime
  return $dateTime;
 
 }[/code]

Use the gmt_dt_to_client_dt($dateTime, $offSet) to convert the time, the offSet is the off set of GMT in hours.

If you want time to to be relative to you from any browser in any time zone, have javascript put the UTC/GMT hour offset in a cookie, then have php read the cookie and use that offset to generate relative dates and times.

Also if you want you could just save date and time in the database as a UNIX TIME STAMP, this would be more efficient, however it makes the raw database entries humanly unreadable.

@nasToneauthorDec 10.2007 — #Here is my exact code. Is this how it is supposed to look. It seems as if the time in my original post was off only by +1 hour. Now, using this code, I am [U]WAY[/U] off. I saved a new entry to the database at 12/9/2007 @ 7:04pm. The entry in the table reads 2007-12-10 01:04:25. The entry format is just what I am after (after combining the date & time fields). The just seems way off. How do I correct this?

[code=php]
 <html>
 <head>
 <title>MySQL Submission Test</title>
 </head>
 <body>
 <?php
 if (!isset($_POST['submit'])) {
 ?>
 <form action="" method="post">
 <center><input type="text" name="user_name"></center>
 <center>Username</center>
 <center> <input type="text" name="password"></center>
 <center>Password</center>
 <center><input type="submit" name="submit" value="Submit!"></center>
 </form>
 <?php
 } else {
 include('dbConfig.php');
 //**********************************************************************************************
 $dateTime = gmdate('Y-m-d H:i:s'); 
 //Create Function: convert unix time stamp to dateTime
 function tS_to_dT($ts) {
 //return dateTime form time stamp
 return gmdate('Y/m/d H:i:s', $ts);
 }
 //Create Function: convert date time to unix time stamp
 function date_time_to_unix_ts($dateTime){
 //separate date from time
 $exp = explode(' ', $dateTime);
 $date = $exp[0];
 $time = $exp[1];
 //if no time make zeros
 if($time=='') {
 $time = '00:00:00';
 }
 //separate year, month and day
 $exp = explode('-', $date);
 $year = $exp[0];
 $month = $exp[1];
 $day = $exp[2];
 //separate hour, minute, and second
 $exp = explode(':', $time);
 $hour = intval($exp[0]);
 $minute = intval($exp[1]);
 $second = intval($exp[2]);
 //return Unix time stamp
 return gmmktime($hour, $minute, $second, $month, $day, $year);
 }
 //Create Function: offet GMT time
 function gmt_dt_to_client_dt($dateTime) {
 //convert dateTime to unix time stamp
 $timeStamp = date_time_to_unix_ts($dateTime);
 //add offset to Time Stamp
 $timeStamp += (-1*3600);
 //change unix time stamp to dateTime
 $dateTime = tS_to_dT($timeStamp);
 //return dateTime
 return $dateTime;
 } 
 //**********************************************************************************************
 $user_name = $_POST['user_name'];
 $password = $_POST['password'];
 //$create_date = date('Y/m/d');
 //$create_time = date('H:i:s');
 mysql_query("INSERT INTO Valid_User (user_name, password, created) VALUES ('$user_name', '$password', '$dateTime')");
 echo "Your request has been added!";
 echo '<meta http-equiv="refresh" content="5;url=http://customdesignsource.com/Auth.php">'; 
 }
 ?>
 </body>
 </html>
 [/code]

@holidayDec 10.2007 — #Stop changing "yyyy-mm-dd" to "yyyy/mm/dd"!

here is how the submission page should look.

[code=php]<html>
 <head>
 <title>MySQL Submission Test</title>
 </head>
 <body>
 <?php
 if (!isset($_POST['submit'])) {
 ?>
 <form action="" method="post">
 <center><input type="text" name="user_name"></center>
 <center>Username</center>
 <center> <input type="text" name="password"></center>
 <center>Password</center>
 <center><input type="submit" name="submit" value="Submit!"></center>
 </form>
 <?php
 } else {
 //include db info
 include('dbConfig.php');
 
 //retrieve POST vars
 $user_name = $_POST['user_name'];
 $password = $_POST['password'];
 
 //get date time
 $dateTime = gmdate('Y-m-d H:i:s');
 
 mysql_query("INSERT INTO Valid_User (user_name, password, created) VALUES ('$user_name', '$password', '$dateTime')");
 echo "Your request has been added!";
 echo '<meta http-equiv="refresh" content="5;url=http://customdesignsource.com/Auth.php">';
 }
 ?>
 </body>
 </html>[/code]

The date and time in the database will GMT time, so it will not be current for you, convert the time to your time when reading from the database. here's an example. I set the offset to -6 based off the info you gave. If this does not output the correct time adjust as needed.

[code=php]<?php
 //include db info
 include('dbConfig.php');
 
 //Create Function: convert unix time stamp to dateTime
 function tS_to_dT($ts) {
 //return dateTime form time stamp
 return gmdate("Y-m-d H:i:s", $ts);
 }
 
 //Create Function: convert date time to unix time stamp
 function date_time_to_unix_ts($dateTime){
 //separate date from time
 $exp = explode(" ", $dateTime);
 $date = $exp[0];
 $time = $exp[1];
 
  //if no time make zeros
  if($time=='') {
   $time = '00:00:00';
  }
 
  //separate year, month and day
  $exp = explode("-", $date);
  $year = $exp[0];
  $month = $exp[1];
  $day = $exp[2];
 
  //separate hour, minute, and second
  $exp = explode(":", $time);
  $hour = intval($exp[0]);
  $minute = intval($exp[1]);
  $second = intval($exp[2]);
 
  //return Unix time stamp
  return gmmktime($hour, $minute, $second, $month, $day, $year);
 }
 
 //Create Function: offet GMT time
 function gmt_dt_to_client_dt($dateTime, $offSet) {
 
  //convert dateTime to unix time stamp
  $timeStamp = date_time_to_unix_ts($dateTime);
 
  //add offset to Time Stamp
  $timeStamp += ($offSet*3600);
 
  //change unix time stamp to dateTime
  $dateTime = tS_to_dT($timeStamp);
 
  //return dateTime
  return $dateTime;
 
 }
 
 //do query and echo resutls
 $results = mysql_query("SELECT * FROM Valid_User");
 while($row = mysql_fetch_array($results)) {
 echo "username: " . $row['user_name'] . " - created on: " . gmt_dt_to_client_dt($row['created'], -6) . "<br />n";
 }
 ?>[/code]

If you don't want the date to be formatted like YYYY-MM-DD here is a function that will change the format (but it should be stored in the database as yyyy-mm-dd)

[code=php]//Create Function: convert date format from "yyyy-mm-dd" to "m/d/yyyy"
 function convert_date_format_simple($date) {
 
  //if date and time: get just date
  $expGDFT = explode(" ", $date);
  $date = $expGDFT[0];
 
  $expl = explode("-", $date);
  $expl[1] = $expl[1]+0;
  $expl[2] = $expl[2]+0;
 
  return $expl[1] . "/" . $expl[2] . "/" . $expl[0];
 }[/code]

@nasToneauthorDec 10.2007 — #GREAT! Works like a charm. Thank you very much!

I was just used to the "yyyy/mm/dd" format from my programming at work. Like I said, I am new to PHP & mySQL. Again, thanks for your help.

@holidayDec 10.2007 — #Any time ;-)

@CARL00Dec 10.2007 — #Hi, I was wondering if you could help me.. im trying to find out the time difference between a previously recorded time and the current time.

The previously recorded time is read from a MYSQL table, and is in the form of the DATETIME of MYSQL.

I used your above mentioned function to convert the DATETIME into unixtime, and then compared it with the current time using the time() function.

$busyTime = /// read from mysql query in the form 2007-12-10 13:42:14

$diff = date_time_to_unix_ts($busyTime) - time();

im getting some weird output.. like 1197294134.This doesnt seem to be the difference in secs either, so im guessing im getting the wrong output. some help please?

EDIT: the time stored in the MYSQL db is already in GMT

@holidayDec 10.2007 — #lol, you subtracted current form past, you need to subtract past from current ? [code=php]$diff = time() - date_time_to_unix_ts($busyTime);[/code]

@CARL00Dec 11.2007 — #I realize what you mean, but i forgot to mention that the recorded time is some time in the future. i.e busytime = you are busy till this time (which in most cases would be later than the current time)

Also in #PHP _↴

interesting concept... can it be done?Only These Allowed!Php Search Help Please

Success!

Help @nasTone spread the word by sharing this article on Twitter...

Tweet This

[RESOLVED] PHP / mySQL time question.

8 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

[RESOLVED] PHP / mySQL time question.

8 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

8 Comments(s) _↴

Also in #PHP _↴