How to get right timezon when using NOW() ?

[3D-kreativ]

Hi,

 

I just wonder how to get the right timezone when i use NOW() in MySQL database? The reason is that the time on the server at the webhotel is -2 hours compared to European time. Strange because the webhotel is located in Denmark. In PHP code you can use date_default_timezone_set("Europe/Stockholm"); , but how to do with NOW() and MySQL?

 

Thanks!

[bh]

Hi,

 

http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html

 

SELECT DATE_ADD(NOW(), INTERVAL 2 HOUR);

SELECT DATE_SUB(NOW(), INTERVAL 1 HOUR);

[3D-kreativ]

Thanks! Hmmm, I'm a little new to SQL, where do I put this code? I got an error when I put it together in a queary that insert values. I use NOW() only when I created the table, so it writes the time and date automatic each time a new row is added to the table.

 

Preciate some help! Thanks!

[bh]

The clearly solution is modifing the mysql time-zone. Else you have to handle all INSERT and UPDATE statements [when you use NOW()] with the example code.

Example: UPDATE table SET updated_time = DATE_SUB(NOW(), INTERVAL 2 HOUR) WHERE id=2;

[3D-kreativ]

OK, But can I change the mysql time-zone on my webhotel?

 

Or if I use  UPDATE table SET updated_time = DATE_SUB(NOW(), INTERVAL 2 HOUR) WHERE id=2; Must I specify the id for the row? Hard to know that in advance?

[bh]

OK, But can I change the mysql time-zone on my webhotel?

Well, i given you a link... 

An easy example: after you connected your database, type this command: SET GLOBAL time_zone = type_here_the_timezone;

After that command your time zone will always be what you typed...

 

Or if I use  UPDATE table SET updated_time = DATE_SUB(NOW(), INTERVAL 2 HOUR) WHERE id=2; Must I specify the id for the row? Hard to know that in advance?

It was only an example

[3D-kreativ]

I preciate your patience, but it doesn't work! This how my queary looks:

 

$query = <<<EOD

 

SET GLOBAL time_zone = 'Stockholm/Europe'

 

INSERT INTO tracker (ip, date, status, userAgent)

 

VALUES ('{$ip}', NOW(), '{$status}', '{$userAgent}');

 

EOD;

 

By the way, you don't happens to know of any good tutorial about creating a blogg? I need basic knowledge about the technical layot and how to think when designing the tables and the joining. I have googled a lot, but haven't found any good so far.

 

Thanks!

[bh]

From the manual:

timezone values can be given in several formats, none of which are case sensitive:

 

The value 'SYSTEM' indicates that the time zone should be the same as the system time zone.

 

The value can be given as a string indicating an offset from UTC, such as '+10:00' or '-6:00'.

 

The value can be given as a named time zone, such as 'Europe/Helsinki', 'US/Eastern', or 'MET'. Named time zones can be used only if the time zone information tables in the mysql database have been created and populated.

 

so...

$query = <<<EOD

SET GLOBAL time_zone = '-2:00';

INSERT INTO tracker (ip, date, status, userAgent)

VALUES ('{$ip}', NOW(), '{$status}', '{$userAgent}');

EOD;

[3D-kreativ]

Hmm, it doesn't work..... I get an error that i can't query database! ?

[bh]

If only this one is in the query is it work?

SET GLOBAL time_zone = '-2:00';

 

What is the error message?

[3D-kreativ]

Well it's just an error message from my code

 

$res = $mysqli->query($query) or die("Could not query database");

 

Perhaps I have wrong data type in the table? I have TIMESTAMP right now

[bh]

timestamp is ok.

just echo

mysql_error();

and we'll see the exact error

[3D-kreativ]

Sorry, but I get the same message!

 

$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);

 

if (mysqli_connect_error()) {

echo mysql_error();

  exit();

}

 

$query = <<<EOD

 

SET GLOBAL time_zone = '-2:00'

 

INSERT INTO tracker (ip, date, status, userAgent)

 

VALUES ('{$ip}', NOW(), '{$status}', '{$userAgent}');

 

EOD;

 

$res = $mysqli->query($query) or die("Could not query database");

 

$mysqli->close();

 

And I just chat with the support at ONE.COM, the webhotel, and they say you can't change time in MySQL! The recomend :

 

date_default_timezone_set('Europe/Stockholm'); in the PHP code. But that doesn't work either!  ?

 

[bh]

$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE); 

$query = <<<EOD

SET GLOBAL time_zone = '-2:00';

INSERT INTO tracker (ip, date, status, userAgent)

VALUES ('{$ip}', NOW(), '{$status}', '{$userAgent}');

EOD;

if ( !  $mysqli->query($query))
{
  echo $mysqli->error();
}

$mysqli->close(); 

 

sorry, i dont see its mysqli... so the get error message is: $mysqli->error();

[3D-kreativ]

The result this time was no error message and no writing to table!

 

After the change I get Fatal error: Call to undefined method mysqli::error() in /customers/ and they refere to line 70 and that is echo $mysqli->error();

[bh]

$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE); 

$query = "SET GLOBAL time_zone = '-2:00';
INSERT INTO tracker (ip, date, status, userAgent)
VALUES ('{$ip}', NOW(), '{$status}', '{$userAgent}');";

if ( !  $mysqli->query($query))
{
  echo $mysqli->error();
}

$mysqli->close(); 

 

if it doesnt work its not the query...

your table structure maybe wrong...

[3D-kreativ]

Thanks, but I have to leave the computer for some hours. Perhaps it can't be solved. I have to use date variables from PHP and write them into the table.

 

Preciate your time you spend on this!

[3D-kreativ]

Here is the structure of the table:

 

CREATE TABLE `tracker` (

  `id` int(11) NOT NULL auto_increment,

  `ip` varchar(50) default NULL,

  `date` timestamp NULL default NULL,

  `status` int(11) default NULL,

  `userAgent` varchar(200) default NULL,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

 

Do you see anything wrong?

 

[bh]

Its ok.

 

And if i run this query its ok too. And the time zone works too.

'+5:00' = add 5 hours to UTC.

 

SET GLOBAL time_zone = '+5:00';
INSERT INTO `tracker` (
`id` ,
`ip` ,
`date` ,
`status` ,
`userAgent`
)
VALUES (
NULL , '10.10.10.10', NOW() , '10101', 'nothing'
);

 

[3D-kreativ]

Sorry, but I'm back! Perhaps we have solved it?!

 

It runs perfect for me when I run it local on my computer with MySQL Workbench and it runs perfect on the school server when I test with PHPMyAdmin, BUT when I test with PHPMyAdmin at ONE.COM, my webhotell, I get this answer:

 

#1227 - Access denied; you need the SUPER privilege for this operation

 

So, I guess it's time to abandon this code and not waste any more time! So I guess I have to use commen variables in PHP and put them into the table!?

[ignace]

SET GLOBAL time_zone = type_here_the_timezone;

 

Only works if you have the SUPER privilege, you don't want to use this account. Instead use

 

SET time_zone = 'Europe/Stockholm'

 

Which sets the time_zone per session. You can however login to your mysql server using the SUPER-account and change the time_zone using the GLOBAL-keyword so you don't have to declare it in your application.

[3D-kreativ]

Hi! Will this be a multi queary? Code like this:

 

<?php

 

require_once('config.php');

 

$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);

 

$query = <<<EOD

SET time_zone = 'Europe/Stockholm'

INSERT INTO `tracker` (

`id` ,

`ip` ,

`date` ,

`status` ,

`userAgent`

)

VALUES (

NULL , '10.10.10.10', NOW() , '10101', 'nothing'

);

EOD;

 

$res = $mysqli->multi_query($query)

                    or die("Could not query database");

 

 

$mysqli->close();

 

?>

[fenway]

No, not a multi-query -- once per page.

[3D-kreativ]

Still doesn't work!  :'(

 

<?php

 

require_once('config.php');

 

$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);

 

$query = <<<EOD

SET time_zone = 'Europe/Stockholm'

 

INSERT INTO `tracker` (

`id` ,

`ip` ,

`date` ,

`status` ,

`userAgent`

)

VALUES (

NULL , '10.10.10.10', NOW() , '10101', 'nothing'

);

EOD;

 

$res = $mysqli->query($query) or die("Could not query database");

 

$mysqli->close();

 

?>

[fenway]

You didn't do as I said.  you're still using multi-query.  So don't.