2

I have a mysql database table that gets the "datetime" column automatically filled when a user fills in the php form page.

$timestamp = datetime("Y-m-d H:i:s");

Trouble is, the database server is in the USA which is 9 hours 30 minutes behind my time.

Does anyone know a way to change this so that the 9 hours 30 minutes hours get added to the datetime?

stema
  • 90,351
  • 20
  • 107
  • 135
Lenin
  • 492
  • 1
  • 8
  • 30
  • 2
    Set the correct timezone in your setting files (php.ini for instance). – Vincent Savard Aug 16 '11 at 05:49
  • thanks for the super fast reply, but i want hours added up while retrieving the time from the db.. – Lenin Aug 16 '11 at 05:51
  • I put "files" as plural because you can also change this in your DBMS. – Vincent Savard Aug 16 '11 at 05:52
  • Several people have suggested setting the correct time zone both for php and the database. This is the correct answer as all the translation from one time zone to another will be handled by the database and require no effort from you. – James Anderson Aug 16 '11 at 06:01

5 Answers5

1

To answer your question directly, you can use the following function in your select statement:

select date_add(datefield, interval 570 minute) from table

However, as stated in some of the comments it's best if you correct the problem from the source and update the timezone in your DBMS.

Jeff
  • 6,643
  • 3
  • 25
  • 35
0

You could always just use date which has an argument for timezone. http://www.php.net/manual/en/function.date.php

liamzebedee
  • 14,010
  • 21
  • 72
  • 118
0

There are several possibilities. See http://www.php.net/manual/en/ref.datetime.php for a list of DateTime-Functions. date_add and date_sub are good candidates to start with

Scoregraphic
  • 7,110
  • 4
  • 42
  • 64
0

date_default_timezone_set().

daGrevis
  • 21,014
  • 37
  • 100
  • 139
0

1) Set the correct timezone in the php.ini file.
2) Set the correct timezone in your DBMS ( MySQL timezone change? for MySQL)

This won't require you to add some weird parameters or constantly use the same functions in your scripts.

Community
  • 1
  • 1
Vincent Savard
  • 34,979
  • 10
  • 68
  • 73
  • the thing is that, that 9 hours is not constant, i want to change it for the locations.. so please suggest a way to add hours to the datetime i retrieve from db.. – Lenin Aug 16 '11 at 05:59
  • @anoop lenin : The link I provided shows how to change the timezone for a connection. Just use `SET time_zone='Europe/Paris';` for instance. – Vincent Savard Aug 16 '11 at 06:01