Organizing dates with times and timezones. (Converting to accurate timestamp.)

Question

I've been picking at my brain for a couple of days with the following conundrum.

I've basically the following complex mySQL table:

    | 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 |

(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.)

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.

All the basics are there but I'm hitting a snag.

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.

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.

So the snag appears when I try to order the dates. 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.

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.

I've tried simply converting back and forth like so:

    $timestamped = strtotime($date);
    $datetimed = date('m/d/Y h:i P',$timestamped);

And it doesn't work, so I guessed it had something to do with the date format I'm using in my table.

So then I tried this:

    $var = DateTime::createFromFormat('m/d/Y H:i P',$date)->getTimestamp();

To no avail, yet...

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.

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.

This is what this example should more or less look like at the end:

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.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.

---

RESOLUTION (THOUGH NOT ANSWER)

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 DateTime::createFromFormat()->getOffset(), which I've now seen my second answerer 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 ... (Paris, GMT+1) and so on.

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 }... 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.)

if(isset($item) && $item['event'] == '1') {
  $event = $item['event'];
  $date  = $item['date'];

  $date_array = date_parse($date);

  $minute = $date_array['minute'];
  if($minute<10) {
    $minute = '0'.$minute;
  }

  $timezone = $item['timezone'];
  if($timezone!=='') {
    $timezone = DateTime::createFromFormat('P',$timezone)->getOffset();
    $timezone = $timezone/-3600;
    if($timezone<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)->getOffset();
    $timezone_real = $timezone_real/-3600;
    if($timezone_real<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']<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'  => 'January', '2'  => 'February', '3'  => 'March', '4'  => 'April', '5'  => 'May', '6'  => 'June', '7'  => 'July', '8'  => 'August', '9'  => 'September', '10' => 'October',  '11' => 'November', '12' => 'December');

$event_date = $date_convert[$date_array['month']].' '.$date_array['day'].', '.$date_array['year'].', '.$hour_convert.':'.$minute.' '.$hour_suffix;

if(($event_date_unformatted-$today)>0) {
  echo '<h5>UPCOMING:</h5>';
  echo '<h6>'.$item['location'].', '.$event_date.' <sup>('.$timezone_real.')</sup></h6>';
}
}

Answer 1

You say that DateTime::createFromFormat doesn't work, but don't tell us what the error message is. I guess its because your format string doesn't represent the format you are passing. See the manual page for acceptable formats.

Having said that, I believe that you were on the right track with DateTime::createFromFormat, that is the approach I would take. Somebody with more powerful SQL foo than mine could probably come up with a way of doing this with a query, but here is my purely PHP approach to getting you an array of events sorted on date:-

//first we connect to our database
$dsn = 'mysql:dbname=stackoverflow;host=127.0.0.1';
$user = '********';
$password = '*********';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

//then we get our list of events from the table
$sql = "select * from datetable";

$select = $dbh->prepare($sql);
$select->execute();

//We now have an associative array of events, but in the wrong order.
$results = $select->fetchAll(PDO::FETCH_ASSOC);

$events = array();

//we want to sort on date, so lets get the dates into a format we can sort on
foreach($results as $result){
    $event = $result;
    $date = explode(' ', $result['date']);
    if(isset($date[1])){
        $event['date'] = \DateTime::createFromFormat('m/d/Y H:i', $date[0] . ' ' . $date[1]);
        $event['tz'] = $date[2];
    } else {
        $event['date'] = \DateTime::createFromFormat('m/d/Y', $date[0]);
    }
    $events[] = $event;
}

//our sorting function
$byDate = function(array $eventA, array $eventB){
    return $eventB['date'] > $eventA['date'] ? -1 : 1;
};

//sort the array
usort($events, $byDate);

//we now have our array sorted correctly
var_dump($events);

Result:-

array (size=5)
  0 => 
    array (size=6)
      'id' => string '2' (length=1)
      'title' => string 'Meeting' (length=7)
      'description' => string 'With reps.' (length=10)
      'event' => string 'Yes' (length=3)
      'date' => 
        object(DateTime)[4]
          public 'date' => string '2013-02-15 09:00:00' (length=19)
          public 'timezone_type' => int 3
          public 'timezone' => string 'Europe/London' (length=13)
      'tz' => string '-05:00' (length=6)
  1 => 
    array (size=5)
      'id' => string '1' (length=1)
      'title' => string 'Big painting' (length=12)
      'description' => string '3-day work' (length=10)
      'event' => string 'No' (length=2)
      'date' => 
        object(DateTime)[3]
          public 'date' => string '2013-03-10 23:18:05' (length=19)
          public 'timezone_type' => int 3
          public 'timezone' => string 'Europe/London' (length=13)
  2 => 
    array (size=5)
      'id' => string '4' (length=1)
      'title' => string 'Round paint.' (length=12)
      'description' => string 'One week' (length=8)
      'event' => string 'No' (length=2)
      'date' => 
        object(DateTime)[6]
          public 'date' => string '2013-04-05 23:18:05' (length=19)
          public 'timezone_type' => int 3
          public 'timezone' => string 'Europe/London' (length=13)
  3 => 
    array (size=6)
      'id' => string '5' (length=1)
      'title' => string 'Conference' (length=10)
      'description' => string 'On Picasso' (length=10)
      'event' => string 'Yes' (length=3)
      'date' => 
        object(DateTime)[7]
          public 'date' => string '2013-04-22 18:00:00' (length=19)
          public 'timezone_type' => int 3
          public 'timezone' => string 'Europe/London' (length=13)
      'tz' => string '-05:00' (length=6)
  4 => 
    array (size=6)
      'id' => string '3' (length=1)
      'title' => string 'Presentation' (length=12)
      'description' => string '5 paintings' (length=11)
      'event' => string 'Yes' (length=3)
      'date' => 
        object(DateTime)[5]
          public 'date' => string '2013-08-02 22:00:00' (length=19)
          public 'timezone_type' => int 3
          public 'timezone' => string 'Europe/London' (length=13)
      'tz' => string '+02:00' (length=6)

The main issue still outstanding is the timezones. Your database stores offsets, not timezones. There are some questions on this on SO (this one for example), so I won't double up on those efforts here, but the offsets are available in the array should you find a way to us them.

I noticed in your comments that you are considering adding a time zone column, this is a good idea. However, I would advise you to store them as TZ strings from this list, they can then be passed directly into the constructor of DateTimeZone to give you advantages such as allowance for daylight savings etc.

Answer 2

The SQL query should be

   $data= mysql_query( "SELECT * FROM (table name) ORDER BY date ASC")

I am adding an algorithm given your latest comments.

Firstly: Your data type for the DATE column needs to be uniform, If it is of the datetime or timestamp format it should order the data correctly.

This link provides you with a comprehensive list of date and time functions. It is worth reading to give you a better feel for solving your problem.

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

See this link that addresses the correct formatting of dates - (however for a cleaner table, I suggest you have only the one format to be accepted into your table):

Order by descending date - month, day and year

For online use: I think somehow you need to either "standardise" the timezones. The timestamp can be created within the same timezone for all events, the user could then view these times according to their chosen timezone., hence all the times are adjusted for each user.

this link discusses session variables for timezones: http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

Per-connection time zones. Each client that connects has its own time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement:

mysql> SET time_zone = timezone;

However for local use, where an individual may receive a flyer of the updates, with a view to attending them, then you need to display the timezone for that locality, with the locality mentioned. Adding for eg GMT +2.00 does not give many users an indication of the time with reference to their own timezone, they would usually have to convert it themselves. This puts an added frustration for the user. It would be worth the effort to convert it for them, or offer some explanation for the time differences - so the user can get a clear understanding of "when" this event is happening with respect to their timezone.

To deal with the timezones, it is worth going to this link: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz

CONVERT_TZ() converts a datetime value dt from the time zone given by from_tz to the time zone given by to_tz and returns the resulting value. Time zones are specified as described in Section 10.6, “MySQL Server Time Zone Support”. This function returns NULL if the arguments are invalid.

If the value falls out of the supported range of the TIMESTAMP type when converted from from_tz to UTC, no conversion occurs. The TIMESTAMP range is described in Section 11.1.2, “Date and Time Type Overview”.

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET'); -> '2004-01-01 13:00:00' mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00'); -> '2004-01-01 22:00:00' Note To use named time zones such as 'MET' or 'Europe/Moscow', the time zone tables must be properly set up. See Section 10.6, “MySQL Server Time Zone Support”, for instructions.

I think if you add an extra column, this will help your problem.

I think by

1. checking your data types
2. standardising your timezones
3. Adding another column to show the timezone

you will have no trouble ordering your table.

I hope this helps. Please let me know.