Databases
StackOverflow2013
Postsdbo
POPHP: right way to manage DateTime (timestamp Y2038 Bug aware!)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPHP: right way to manage DateTime (timestamp Y2038 Bug aware!)
    text
    copied!<p>In my "tool box" i'm using this function:</p> <pre><code>function dataAttuale() { $now = new DateTime(); $dataAttuale = $now-&gt;format(DateTime::ISO8601); $offset = $now-&gt;getOffset(); date_default_timezone_set('UTC'); $nowUTC = new DateTime(); $dataUTC = $nowUTC-&gt;format(DateTime::ISO8601); $orario = array(); $orario['dataAttuale'] = $dataAttuale; $orario['dataUTC'] = $dataUTC; $orario['offset'] = $offset; return $orario; } </code></pre> <p>I get this array</p> <pre><code>Array ( [dataAttuale] =&gt; 2013-10-18T11:03:52+0200 [dataUTC] =&gt; 2013-10-18T09:03:52+0000 [offset] =&gt; 7200 ) </code></pre> <p>So i could save in a datetime MySql field a datetime referred to UTC.</p> <p>Now, i've some trouble about this.</p> <p>1) I would save also offset (in seconds). <strong>What's best Mysql field</strong>? I think max seconds can be +14hour * 60 * 60 = 50400 and -12hours*60*60 = -43200</p> <p>2) Do you think is notable save also offset? I.e., for example, several API services return a date in UTC + offset...</p> <p>Thank you very much!</p> <p>UPDATE:</p> <p>Thank you to both people. Now i'm saving in MySQL datetime in UTC format and varchar timezone. With a couple of code I'm getting what I want:</p> <pre><code>$orario = new DateTime($value['creazione'], new DateTimeZone($value['timezone'])); $orario = $orario-&gt;format(DateTime::ISO8601); </code></pre> <p>The output is (for Europe/Rome)</p> <pre><code>2013-10-19T09:27:54+0200 </code></pre> <p>And for America/Montreal</p> <pre><code>2013-10-19T09:29:16-0400 </code></pre> <p>And for Australia/Melbourne</p> <pre><code>2013-10-19T09:30:31+1100 </code></pre> <p>(difference of minutes//seconds it the time to change in my PHP scripts the default Timezone).</p> <p>Now I think that:</p> <p>1) I can laugh about Y2038 bug, abandoning (sigh :( ) timestamp :(</p> <p>2) I can safely travel around the world and use my own Calendar (naaaa... i'll use forever Google Calendar, of course)</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload