timestamp in server giving wrong time even after setting timezone in php

Question

i have a website which adds values to table after the user fills the form. My server is outside India, so when i put current timestamp its not giving me correct Indian time, so to fix it i have added Indian timezone in my php file. the column default value is still "Currenttimestamp" The complete code is like below:

<?php

session_start();

error_reporting(0);
include('includes/dbconnection.php');
if (strlen($_SESSION['cvmsaid']==0)) {
  header('location:logout.php');
  } else{
    if(isset($_POST['submit']))
  {



$cvmsaid=$_SESSION['cvmsaid'];
 $fullname=$_POST['fullname'];

$mobnumber=$_POST['mobilenumber'];
$email=$_POST['email'];
$add=$_POST['address'];
$whomtomeet=$_POST['whomtomeet'];
$department=$_POST['department'];
$reasontomeet=$_POST['reasontomeet'];

$query=mysqli_query($con,"set session time_zone='Asia/Kolkata'");
$query=mysqli_query($con, "insert into tblvisitor(FullName,Email,MobileNumber,Address,WhomtoMeet,Deptartment,ReasontoMeet) value('$fullname','$email','$mobnumber','$add','$whomtomeet','$department','$reasontomeet')");

    if ($query) {
    $msg="Visitors Detail has been added.";
  }
  else
    {
      $msg="Something Went Wrong. Please try again";
    }


}

?>
  <!DOCTYPE html>
  <html lang="en">

  <head>


  </head>

  <body class="animsition">
    <div class="page-wrapper">
      <!-- HEADER MOBILE-->
      <?php include_once('includes/sidebar.php');?>

      <div class="page-container">
        <!-- HEADER DESKTOP-->
        <?php include_once('includes/header.php');?>
        <!-- HEADER DESKTOP-->

        <!-- MAIN CONTENT-->
        <div class="main-content">
          <div class="section__content section__content--p30">
            <div class="container-fluid">
              <div class="row">

                <div class="col-lg-12">
                  <div class="card">
                    <div class="card-header">
                      <strong>Add</strong> New Visitor
                    </div>
                    <div class="card-body card-block">
                      <form action="" method="post" enctype="multipart/form-data" class="form-horizontal">
                        <p style="font-size:16px; color:red" align="center">
                          <?php if($msg){
    echo $msg;
  }  ?> </p>
                        <div class="row form-group">
                          <div class="col col-md-3">
                            <label for="text-input" class=" form-control-label">Full Name</label>
                          </div>
                          <div class="col-12 col-md-9">
                            <input type="text" id="fullname" name="fullname" placeholder="Full Name" class="form-control" required="">

                          </div>
                        </div>
                        <div class="row form-group">
                          <div class="col col-md-3">
                            <label for="email-input" class=" form-control-label">Email Input</label>
                          </div>
                          <div class="col-12 col-md-9">
                            <input type="email" id="email" name="email" placeholder="Enter Email" class="form-control" required="">

                          </div>
                        </div>
                        <div class="row form-group">
                          <div class="col col-md-3">
                            <label for="password-input" class=" form-control-label">Phone Number</label>
                          </div>
                          <div class="col-12 col-md-9">
                            <input type="text" id="mobilenumber" name="mobilenumber" placeholder="Mobile Number" class="form-control" maxlength="10" required="">

                          </div>
                        </div>

                        <div class="row form-group">
                          <div class="col col-md-3">
                            <label for="textarea-input" class=" form-control-label">Address</label>
                          </div>
                          <div class="col-12 col-md-9">
                            <textarea name="address" id="address" rows="9" placeholder="Enter Visitor Address..." class="form-control" required=""></textarea>
                          </div>
                        </div>
                        <div class="row form-group">
                          <div class="col col-md-3">
                            <label for="password-input" class=" form-control-label">Whom to Meet</label>
                          </div>
                          <div class="col-12 col-md-9">
                            <input type="text" id="whomtomeet" name="whomtomeet" placeholder="Whom to Meet" class="form-control" required="">

                          </div>
                        </div>
                        <div class="row form-group">
                          <div class="col col-md-3">
                            <label for="password-input" class=" form-control-label">Company</label>
                          </div>
                          <div class="col-12 col-md-9">
                            <input type="text" id="department" name="department" placeholder="Company Name" class="form-control" required="">

                          </div>
                        </div>
                        <div class="row form-group">
                          <div class="col col-md-3">
                            <label for="password-input" class=" form-control-label">Reason To Meet</label>
                          </div>
                          <div class="col-12 col-md-9">
                            <input type="text" id="reasontomeet" name="reasontomeet" placeholder="Reason To Meet" class="form-control" required="">

                          </div>
                        </div>

                        <div class="card-footer">
                          <p style="text-align: center;"><button type="submit" name="submit" id="submit" class="btn btn-primary btn-sm">Submit
                                        </button></p>

                        </div>
                      </form>
                    </div>

                  </div>

                </div>

              </div>


              <?php include_once('includes/footer.php');?>
            </div>
          </div>
        </div>
      </div>
    </div>


  </body>

  </html>
  <!-- end document-->
  <?php }  ?>

Still am not getting the Indian timestamp, can anyone please tell me how to fix it. Thanks in advance

Check it - https://stackoverflow.com/questions/32224547/setting-the-timezone-for-php-in-the-php-ini-file — Dmitry Leiko, Dec 28 '19 at 11:40
If the column is truly a TIMESTAMP, all you need to do before retrieving it is to make sure you first execute `set session time_zone='Asia/Kolkata' on the same connection. Are you doing this? — Booboo, Dec 28 '19 at 11:44
I edited you code and broke your query into two queries. I also changed your code snippet, which is meant for JavaScript/HTML to be executed, into a standard code block. — Booboo, Dec 28 '19 at 12:59
@Booboo i have added your code, but still the timestamp is not changed, its showing the server timestamp — kingkhan kkhan, Dec 28 '19 at 13:02
Did I mess up your code? I no longer see the select statement. If I did, could you put the code back? Do you by any chance do another `mysqi_connect` before doing the select? — Booboo, Dec 28 '19 at 13:11
Then wherever you do select the column for display, as I said in a comment to my answer, is where you need to set the time zone as I have done in the code above (and remove your PHP time manipulation code). If you run a MySql console and issue a `set session time_zone='Asia/Kolkata';` command and display the row in question, it will look the way you want. And in my answer I state "before **retrieving** the column". — Booboo, Dec 28 '19 at 13:22
And to repeat: The time zone setting is not important for TIMESTAMP fields that are initialized with values such as NOW() or CURRENT_TIMESTAMP(). The setting is important for when you are retrieving these fields for display. If, however, you were assigning a *literal* value such as `'2019-12-27 11:12:13'` to a TIMESTAMP column *and* you wanted that to be interpreted in the Kolkata time zone, then you *would* have to first set the session time zone first before storing that value. — Booboo, Dec 28 '19 at 13:31
See my post at: https://stackoverflow.com/questions/31761047/what-difference-between-the-date-time-datetime-and-timestamp-types — Booboo, Dec 28 '19 at 13:34

Booboo · Accepted Answer · 2019-12-28T11:52:59.400

1

If the column is a TIMESTAMP, then all you need to do on the connection, $con, before retrieving the column, is execute the following query:

set session time_zone='Asia/Kolkata'

This works for whatever language you are using. You should not have do any any manipulations in PHP on the returned timestamp as far as time zone conversions; these will be done by MySql for you.

By the way, you should use prepared statements. As you currently are executing your query, you are leaving yourself open to a possible SQL Injection attack.

edited Dec 28 '19 at 11:52

answered Dec 28 '19 at 11:47

Booboo

38,656
3
37
60

it doesnt solve the issue, its showing Parse error: syntax error, unexpected 'session' (T_STRING) – kingkhan kkhan Dec 28 '19 at 12:12
Then you are not doing it correctly if you are getting a syntax error: mysql> `set session time_zone='Asia/Kolkata'; Query OK, 0 rows affected (0.00 sec)` – Booboo Dec 28 '19 at 12:22
Some servers could be missing certain time zones, but you would not get a syntax error, but rather: `ERROR 1298 (HY000): Unknown or incorrect time zone: 'Asia/Kolkatax'` – Booboo Dec 28 '19 at 12:24
@kingkhankkhan Did you include the word `set`? – Booboo Dec 28 '19 at 12:27
where excatly should i add it – kingkhan kkhan Dec 28 '19 at 12:32
should i add it like this – kingkhan kkhan Dec 28 '19 at 12:35
@kingkhankkhan *After* you acquire the connection (with `mysqli_connect`), then: `$query=mysqli_query($con,"set session time_zone='Asia/Kolkata'");` – Booboo Dec 28 '19 at 12:42
you mean like this $query=mysqli_query($con,"set session time_zone='Asia/Kolkata''","insert into tblvisitor(FullName,Email,MobileNumber,Address,WhomtoMeet,Deptartment,ReasontoMeet) value('$fullname','$email','$mobnumber','$add','$whomtomeet','$department','$reasontomeet')"); – kingkhan kkhan Dec 28 '19 at 12:44
the query is not executing – kingkhan kkhan Dec 28 '19 at 12:46
@kingkhankkhan The time zone setting is not important for TIMESTAMP fields that are initialized with values such as `NOW()` or `CURRENT_TIMESTAMP()`. The setting is important for when you are *retrieving* these fields for display. – Booboo Dec 28 '19 at 12:47
can you please help me in fixing it – kingkhan kkhan Dec 28 '19 at 12:48
Update your code so I can see exactly how you are setting the time zone, you can do this right before the select statement. Also, am I correct in assuming that the TIMESTAMP column is initialized with either `NOW()` or `CURRENT_TIMESTAMP()`? – Booboo Dec 28 '19 at 12:50
i have updated the code in my question according to your suggestion. yes the column is current_timestamp – kingkhan kkhan Dec 28 '19 at 12:53

score 0 · Answer 2 · answered Dec 28 '19 at 11:43

0

Try to create a custom php.ini in your public_html folder with your new config

and add to it

date.timezone="Asia/Kolkata"

after editing try to check if change correct by

echo date_default_timezone_get();

answered Dec 28 '19 at 11:43

Joseph

5,644
3
18
44

timestamp in server giving wrong time even after setting timezone in php

2 Answers2