title: Organizing dates with times and timezones. (Converting to accurate timestamp.)
body: <p>I've been picking at my brain for a couple of days with the following conundrum.</p>
  
  <p>I've basically the following complex mySQL table:</p>
  
  <pre><code>    | ID |   TITLE      |    DESCRIPTION    | EVENT |      DATE               |
      |----|--------------|-------------------|-------|-------------------------|
      |  1 | Big painting | 3-day work        |  No   | 03/10/2013              |
      |  2 | Meeting      | With reps.        |  Yes  | 02/15/2013 09:00 -05:00 |
      |  3 | Presentation | 5 paintings       |  Yes  | 08/02/2013 22:00 +02:00 |
      |  4 | Round paint. | One week          |  No   | 04/05/2013              |
      |  5 | Conference   | On Picasso        |  Yes  | 04/22/2013 18:00 -05:00 |
  </code></pre>
  
  <p><strong>(EDIT: Perhaps I need to clarify that the DATE column is not set as DATE or DATETIME [due to the need to include the Timezone in the data] but rather as VARCHAR, which is why the organization is complicated from the start.)</strong></p>
  
  <p>As you can see, it's a table which comprises all "What's New" items for a painter, for example. The idea is that on this painter's website PHP will call out values in order to display them as a "What's New" news ticker.</p>
  
  <p>All the basics are there but I'm hitting a snag.</p>
  
  <p>Initially, I would like to filter and order my data at the SELECT stage, but I was having trouble doing that. As you can see, bot the event and non-event items have dates, but the non-event items just have them as a way to organize the overall data. The specific date and time is only important for the events, because that way the user will be able to know when these things are occurring. So the basic idea would be to pick out from the table a certain number of the LATEST items. So all items would be read from the table, placed in order of DATE, and then, say, 20 of them would be brought out.</p>
  
  <p>As I said, I had initially though of doing this at the SELECT stage, but I think it might be too complex. So I just extracted all items and set a PHP code after them in order to filter them.</p>
  
  <p><strong>So the snag appears when I try to order the dates.</strong> I'm trying to convert dates to timestamps for the ordering process, then back to dates for the displaying. However, I can't get the date-timestamp or timestamp-date (or both) conversion to work. I always end up with dates that are different to those I started with.</p>
  
  <p>As you can see, the entire thing is made more complex because of the Timezones, which are very important, as a user must be able to know where these things are happening or, rather, at what time according to where they're happening.</p>
  
  <p>I've tried simply converting back and forth like so:</p>
  
  <pre><code>    $timestamped = strtotime($date);
      $datetimed = date('m/d/Y h:i P',$timestamped);
  </code></pre>
  
  <p>And it doesn't work, so I guessed it had something to do with the date format I'm using in my table.</p>
  
  <p>So then I tried this:</p>
  
  <pre><code>    $var = DateTime::createFromFormat('m/d/Y H:i P',$date)-&gt;getTimestamp();
  </code></pre>
  
  <p>To no avail, yet...</p>
  
  <p>I'm thinking that perhaps I should rather set the timestamp at the beginning of the process, i.e. when inserting the data items. But, here also, I would need to convert a "human" date to a timestamp, and if I can't manage this correctly, nothing will properly work.</p>
  
  <p>I understand this question is complex and perhaps my explanation isn't the clearest! Perhaps an output example might help. What I'm trying to achieve is a "What's New" news ticker that includes a list of things that are going on: some of them just information (like status updates, lets say) without visible dates (here dates are only for internal organization; this is where the EVENT column comes in, as it filters which items must show their dates and which must not), others, actual events to which the user is invited and which display their dates for the user. I even have a code that calculates whether the date and time is in the past or the future in order to display an "UPCOMING" tag on those events that are not yet past. However, I'm having trouble handling the dates and ordering them.</p>
  
  <p>This is what this example should more or less look like at the end:</p>
  
  <p><img src="https://i.stack.imgur.com/KMW28.jpg" alt="Example of finished product"></p>
  
  <p>Any and all help will be GREATLY appreciated! (As well as feedback on what you guys think will be the most practical and most clean/elegant/pro way of handling this data retrieval/organization process... if at data input, if at mySQL SELECT stage, if later, etc.)</p>
  
  <p><strong>P.S. I might perhaps add that in the same table I handle other data. This specific "What's New" data is selected by a SELECT function that looks for a specific WHATSNEW column to have a value of TRUE in any row that will be retrieved for this specific "What's New" news ticker.</strong></p>
  
  <hr>
  
  <h2>RESOLUTION (THOUGH NOT ANSWER)</h2>
  
  <p>Because the question was about organizing times and timezones as one string, so to speak, then I'm not sure I can mark any of these two great answers as correct for that specific issue. What I did end up doing was stripping the timezones and putting them in a separate column. Then I formatted my date column as Datetime. So I had that solved, because mySQL Select could take care of the order for me. Now, about the timezones, I ended up cheating a bit: I figured our "artist" couldn't possibly be at two events in two different timezones at the same time, so, really, it's rather improbable that we would need to order two events that are so close together that the timezones of each make a real difference which comes first. So I just have the thing order itself by dates and then whipped up a snippet to take care of the timezones and turn them into "GMT+1", "GMT-5" displays, so the users will know where the location is of that local time. I ended up using <code>DateTime::createFromFormat()-&gt;getOffset()</code>, which I've now seen my second <em>answerer</em> recommended, and said I was on the right track, so I'm happy I kept it in there In order to further clarify this, I added a Location column, where the webmaster will be able to specify the city, say "Paris", say "London", and so on. So the user will end up having something very similar to that which is shown in my example, except that it will say <code>... (Paris, GMT+1)</code> and so on.</p>
  
  <p>Anyway, for anyone out there that has the exact same issue and ends up thinking the exact same things and that this way out is more practical, here goes the heart of the code I ended up with. The rest is just "fill-in". Enjoy! And thanks to both darling persons who were so kind as to take time from their days to help me out in finding a resolution for this issue! (I may have an extra <code>}</code>... sorry for that. The re-formatting when one pastes it into SO is really tedious! I've revised the code twice and can't find any problems, though.)</p>
  
  <pre><code>if(isset($item) &amp;&amp; $item['event'] == '1') {
    $event = $item['event'];
    $date  = $item['date'];
  
    $date_array = date_parse($date);
  
    $minute = $date_array['minute'];
    if($minute&lt;10) {
      $minute = '0'.$minute;
    }
  
    $timezone = $item['timezone'];
    if($timezone!=='') {
      $timezone = DateTime::createFromFormat('P',$timezone)-&gt;getOffset();
      $timezone = $timezone/-3600;
      if($timezone&lt;0) {
        $timezone = $timezone;
      } else
      if($timezone==0) {
        $timezone = '-0';
      } else {
        $timezone = '+'.$timezone;
      }
      $timezone = 'Etc/GMT'.$timezone;
  
      $timezone_real = $item['timezone'];
      $timezone_real = DateTime::createFromFormat('P',$timezone)-&gt;getOffset();
      $timezone_real = $timezone_real/-3600;
      if($timezone_real&lt;0) {
        $timezone_real = str_replace('-','+',$timezone_real);//.':00';
      } else
      if($timezone_real==0) {
        $timezone_real = '+0';//:00';
      } else {
        $timezone_real = '-'.$timezone_real;//.':00';
      }
      $timezone_real = 'GMT'.$timezone_real;
  
      date_default_timezone_set($timezone);
    }
  
  $today = date('n/j/Y G:i', time());
  $today = strtotime($today);
  $event_date = $date_array['month'].'/'.$date_array['day'].'/'.$date_array['year'].' '.$date_array['hour'].':'.$minute;
  $event_date_unformatted = strtotime($event_date);
  
  if($date_array['hour'] == '0') {
    $hour_convert = '12';
    $hour_suffix = 'a.m.';
  } else if($date_array['hour']&lt;12) {
    $hour_convert = $date_array['hour'];
    $hour_suffix = 'a.m.';
  } else if($date_array['hour'] == '12') {
    $hour_convert = $date_array['hour'];
    $hour_suffix = 'p.m.';
  } else {
    $hour_convert = $date_array['hour']-12;
    $hour_suffix = 'p.m.';
  }
  
  $date_convert = array('1'  =&gt; 'January', '2'  =&gt; 'February', '3'  =&gt; 'March', '4'  =&gt; 'April', '5'  =&gt; 'May', '6'  =&gt; 'June', '7'  =&gt; 'July', '8'  =&gt; 'August', '9'  =&gt; 'September', '10' =&gt; 'October',  '11' =&gt; 'November', '12' =&gt; 'December');
  
  $event_date = $date_convert[$date_array['month']].' '.$date_array['day'].', '.$date_array['year'].', '.$hour_convert.':'.$minute.' '.$hour_suffix;
  
  if(($event_date_unformatted-$today)&gt;0) {
    echo '&lt;h5&gt;UPCOMING:&lt;/h5&gt;';
    echo '&lt;h6&gt;'.$item['location'].', '.$event_date.' &lt;sup&gt;('.$timezone_real.')&lt;/sup&gt;&lt;/h6&gt;';
  }
  }
  </code></pre>