Board index   FAQ   Search  
Register  Login
Board index PHP PHP General

Issue with Timestamp in MySQL Database

General discussions related to php

Moderators: macek, egami, gesf

Issue with Timestamp in MySQL Database

Postby gschimek » Sun Jun 15, 2014 5:24 pm

I've got some PHP code that is inserting records into a MySQL database. I've noticed that the timestamp is 1 hour ahead of my actual time. I suspect it's a Daylight Saving Time issue.

I've talked to my webhost and they suggested setting the timezone for each session. I've tried with the following code:

Code: Select all
mysql_select_db($database_mydatabase, $mydatabase);
mysql_query("SET time_zone = America/Chicago");
$query="INSERT INTO tblRegistrations (firstName,lastName)
VALUES ('".$_POST["firstName"]."','".$_POST["lastName"]."')";
$result=mysql_query($query,$mydatabase) or die(mysql_error());


The table has (among others) three columns: timeStamp, firstName, lastName. The timeStamp column is set to have a default value of CURRENT_TIMESTAMP.

Can anyone offer me any suggestions on what to do to solve this problem?
gschimek
New php-forum User
New php-forum User
 
Posts: 2
Joined: Sun Jun 15, 2014 5:17 pm

Re: Issue with Timestamp in MySQL Database

Postby Strider64 » Wed Jun 18, 2014 8:00 am

This is done in php

Code: Select all
date_default_timezone_set('America/Chicago'); // Set the Default Time Zone:


and this is my query that is done using PDO

Code: Select all
$query = 'INSERT INTO movieTrivia (confirm, question, answerA, answerB, answerC, answerD, correct, dateAdded ) VALUES (:confirm, :question, :answerA, :answerB, :answerC, :answerD, :correct, NOW())';
User avatar
Strider64
New php-forum User
New php-forum User
 
Posts: 62
Joined: Sat Mar 23, 2013 8:24 am

Re: Issue with Timestamp in MySQL Database

Postby gschimek » Wed Jun 18, 2014 8:10 am

I thought that was it, too. I've already got that line in my PHP code, but it doesn't seem to be passing through correctly.

I figured out a workaround. I'm sure there are more elegant solutions, but I realized that if I asked PHP to echo back the current date/time, it was correct. It was just when I passed that to MySQL that it was off by an hour. So I just assigned the current date/time to a variable, and inserted that variable to MySQL. It seems to work just fine that way.

Code: Select all
$currentTime = date("Y-m-d H:i:s");
mysql_select_db($database_mydatabase, $mydatabase);
$query="INSERT INTO tblRegistrations (timeStamp,firstName,lastName)
VALUES ('$currentTime','".$_POST["firstName"]."','".$_POST["lastName"]."')";
$result=mysql_query($query,$mydatabase) or die(mysql_error());


Thanks for the response.
gschimek
New php-forum User
New php-forum User
 
Posts: 2
Joined: Sun Jun 15, 2014 5:17 pm


Return to PHP General

Who is online

Users browsing this forum: No registered users and 2 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.