CODEWITHSUNDEEP
phpmysqlmysqli
sonam Sharma
sonam Sharma

Reputation: 558

now() function in mysqli php

I'm using now() to insert the current date time in the database while inserting/updating a table. The code is working fine, but there is only one issue, the date time inserted by php is not in Indian time. I think it is taking the default UTC time.

I have used

date_default_timezone_set('Asia/Calcutta');

 insert into emp_req (ereid,erdid,ersdate,erstime,eredate,eretime,ercid,erattach,ersub,ermess,erdate,ercats,erip) values ('$eid','$dept','$sdate','$stime','$edate','$etime','$geputcomp','$attach','$sub','$message',now(),'$cats','$ip_user')

but in spite of using the default date time, I am getting another time ...

Please note - I can not use the date function, because I have use the same code in other places and I want a solution for this without change the now() function.

Upvotes: 1

Views: 2140

Answers (4)

nshah143
nshah143

Reputation: 559

After you open the connection to MySQL, run the following as a query:

SET time_zone = timezone;

All Connections will use the timezone set until the connection is not closed. So you should set this wherever you make a connection to the database. This way probably you don`t need to make a change anywhere else.

You can also set it globally if you have the permissions.

By default, the option is 'SYSTEM' which is how your system time zone is set (which may or may not be UTC!):

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2012-09-25 16:28:45 |
+---------------------+
1 row in set (0.00 sec)

You can set this dynamically:

mysql> SET @@session.time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | +00:00              |
+--------------------+---------------------+
1 row in set (0.00 sec)

Or permanently in your my.cnf:

[mysqld]
**other variables**
default_time_zone='+00:00'

Restart your server, and you will see the change:

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| +00:00             | +00:00              |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2012-09-25 20:27:50 |
+---------------------+
1 row in set (0.01 sec)

Upvotes: 2

Rajdeep Paul
Rajdeep Paul

Reputation: 16963

You can use MySQL's DATE_ADD function to add 5 hours 30 minutes to the current UTC time,

DATE_ADD(NOW(), INTERVAL '05:30' HOUR_MINUTE)

So your query should be like this,

INSERT INTO emp_req (ereid,erdid,ersdate,erstime,eredate,eretime,ercid,erattach,ersub,ermess,erdate,ercats,erip) VALUES('$eid','$dept','$sdate','$stime','$edate','$etime','$geputcomp','$attach','$sub','$message',DATE_ADD(NOW(), INTERVAL '05:30' HOUR_MINUTE),'$cats','$ip_user')

Upvotes: 0

Ronak Patel
Ronak Patel

Reputation: 305

Run this query for set time zone in mysql

SET GLOBAL time_zone = 'Asia/Calcutta';

Upvotes: 1

Nimesh Patel
Nimesh Patel

Reputation: 804

Try this...

 date_default_timezone_set('Asia/Kolkata');
     $date = date("Y-m-d H:i:s");

    insert into emp_req (ereid,erdid,ersdate,erstime,eredate,eretime,ercid,erattach,ersub,ermess,erdate,ercats,erip) values ('$eid','$dept','$sdate','$stime','$edate','$etime','$geputcomp','$attach','$sub','$message','$date','$cats','$ip_user')

Upvotes: 0

Related Questions