Inserting current time into mysql database based on particular timezone

Question

In my MySql database the value of post_time field has been set to NOT NULL DEFAULT CURRENT_TIMESTAMP

I use the following MySql insert syntax and html form for collecting data from the visitors where date & time of the post is inserted into MySql database through a hidden field when they submit the form.

$insertSQL = sprintf("INSERT INTO entry (post, post_time) VALUES (%s, %s)",                     
GetSQLValueString($_POST['post'], "text"),  
GetSQLValueString($_POST['post_time'], "date"));

  <form action="<?php echo $editFormAction;?>" method="post" name="form1" id="form1">
    <table width="50%" align="center">
      <tr valign="baseline">
        <td align="right" nowrap="nowrap">Post:</td>
        <td>
        <textarea name="post" cols="50" rows="5"></textarea>
        </td>
      </tr>
      <tr valign="baseline">
      <td align="right" nowrap="nowrap">
        <input type="hidden" name="post_time" value="<?php echo $_POST['TIMESTAMP DEFAULT CURRENT_TIMESTAMP']; ?>" /> 
         <input type="hidden" name="MM_insert" value="form1" />
        </td>
        <td>
 <input type="submit" name="submit_post" value="Submit Post" />
       </td>
       </tr>
    </table>
  </form>

How can I collect data about the date & time based on a predefined timezone on a shared server (The server has GMT based time zone)?

How can I execute the following sql syntax then?

Edited:

SET time_zone = 'America/New_York';

Thanks,

This is [probably](http://stackoverflow.com/questions/4458180/php-getsqlvaluestring-function) using `mysql_query`, which means it's using a deprecated function that's going to be removed in future versions of PHP. Doing escaping like this is risky. [Learning PDO](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/) is not hard and will make your application compatible with newer versions of PHP. — tadman, Jun 27 '13 at 15:38
PDO is a little more capable than `mysqli`, it supports named placeholders which make your queries more readable. `mysqli` is good for those rare occasions where PDO is not available. — tadman, Jun 27 '13 at 15:40

score 1 · Answer 1 · answered Jun 27 '13 at 15:39

1

Setting post_time to the default of CURRENT_TIMESTAMP, MySQL will update that column automatically. You do not need to set it unless you want to overwrite it.

Setting the date_default_timezone_set() won't matter for MySQL - you'll need to set the timezone for the MySQL server if it sets its own dates.

Lastly, you shouldn't put the post_time hidden field in the form - your server should either (A) generate this value, or (B) let MySQL generate this value automatically.

answered Jun 27 '13 at 15:39

Rob W

9,134
1
30
50

How shall I change timezone of MySql database on a shared server in this case? Thanks for the other helpful information. – Klanto Aguntuk Jun 27 '13 at 15:52
Either in the `my.ini` `default-time-zone='timezone'`, or set it GLOBALLY in a query `SET GLOBAL time_zone = timezone;`. To set it on a per-connection instance, set it via `SET time_zone = timezone;`. Time zones can be found here: http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html – Rob W Jun 27 '13 at 15:57
It's better to keep all times in the database in UTC and render them in local time as required. – tadman Jun 27 '13 at 15:59
1

I can't do that globally since I don't have master privilege. Where my.ini on a shared server is available? I'm curious. I know this problem can be solved by simply writing `.htaccess` in the root directory. – Klanto Aguntuk Jun 27 '13 at 16:01
^ I agree; +1; OP: You can always convert the date being pulled from MySQL on the application side. – Rob W Jun 27 '13 at 16:02
@Rob W I've edited my question. That was a mistake of my subconscious mind as I had been going through many possible solutions while I was posting my question. – Klanto Aguntuk Jun 27 '13 at 18:00

score 1 · Answer 2 · answered Jun 27 '13 at 15:39

1

You shouldn't be inserting values like that, but instead writing a query that does it:

INSERT INTO entry (post, post_time) VALUES (?, UTC_TIMESTAMP())

The value you're putting in there is how you define a column, not how you insert a value.

answered Jun 27 '13 at 15:39

tadman

208,517
23
234
262

Inserting current time into mysql database based on particular timezone

2 Answers2