Asked  1 Year ago    Answers:  5   Viewed   23 times

Did anyone else had this strange problem? Error message:

Fatal error: Uncaught exception 'Exception' with message 'DateTime::__construct(): Failed to parse time string (01/18/2016 00:00 AM America/New_York) at position 17 (A): The timezone could not be found in the database'

Exception: DateTime::__construct(): Failed to parse time string (01/18/2016 00:00 AM America/New_York) at position 17 (A): The timezone could not be found in the database

Original PHP Code:

$datetime = new DateTime(trim(html_entity_decode($this->input->post('publish_date').' '.$_POST['schedule_time'].' '.$_POST['schedule_meridian'] . ' ' .$_POST['schedule_timezone'])));
$date = $datetime->format('D, d M Y H:i:s O');

 Answers

4

I afraid you've created DateTime object like this:

$date = new DateTime('01/18/2016 00:00 AM America/New_York');

That is not a supported/valid datetime format!

If you want to create a DateTime object from another format you must call DateTime::createFromFormat() instead, look:

$timezone = new DateTimeZone('America/New_York');
$strdate  = '01/18/2016 00:00 AM';
$date     = DateTime::createFromFormat('m/d/Y H:i A', $strdate, $timezone);

PHP doc states:

DateTime::createFromFormat / date_create_from_format — Returns new DateTime object formatted according to the specified format

Thursday, April 1, 2021
 
Tucker
 
3

DateInterval is buggy on windows platform. See bug #51183. The official answer seems to be "use VC9 builds instead for now".

Thursday, April 1, 2021
 
relipse
 
3

I use following wrapper class in my php5.2 apps: http://pastebin.ca/2051944. Untill php5.3 was released - it saves much my time

Saturday, May 29, 2021
 
TuomasR
 
5

Hugo's answer is mostly correct, but I'll add a few key points:

  • When you're storing the customer's time zone, do NOT store a numerical offset. As others have pointed out, the offset from UTC is only for a single point in time, and can easily change for DST and for other reasons. Instead, you should store a time zone identifier, preferably an IANA time zone identifier as a string, such as "America/Los_Angeles". Read more in the timezone tag wiki.

  • Your OrderDateTime field should absolutely represent the time in UTC. However, depending on your database platform, you have several choices for how to store this.

    • For example, if using Microsoft SQL Server, a good approach is to store the local time in a datetimeoffset column, which preserves the offset from UTC. Note that any index you create on that column will be based on the UTC equivalent, so you will get good query performance when doing your range query.

    • If using other database platforms, you may instead wish to store the UTC value in a timestamp field. Some databases also have timestamp with time zone, but understand that it doesn't mean it stores the time zone or offset, it just means that it can do conversions for you implicitly as you store and retrieve values. If you intend to always represent UTC, then often timestamp (without time zone) or just datetime is more appropriate.

  • Since either of the above methods will store a UTC time, you'll also need to consider how to perform operations that need an index of local time values. For example, you might need to create a daily report, based on the day of the user's time zone. For that, you'd need to group by the local date. If you try to compute that at query time from your UTC value, you'll end up scanning the entire table.

    A good approach to deal with this is to create a separate column for the local date (or perhaps even the local datetime depending on your needs, but not a datetimeoffset or timestamp). This could be a completely isolated column that you populate separately, or it could be a computed/calculated column based on your other column. Use this column in an index so you can filter or group by local date.

  • If you go for the computed-column approach, you'll need to know how to convert between time zones in the database. Some databases have a convert_tz function built-in that understands IANA time zone identifiers.

    If you're using Microsoft SQL Server, you can use the new AT TIME ZONE function in SQL 2016 and Azure SQL DB, but that only works with Microsoft time zone identifiers. To use IANA time zone identifiers, you'll need a third party solution, such as my SQL Server Time Zone Support project.

  • At query time, avoid using the BETWEEN statement. It is fully inclusive. It works ok for whole dates, but when you have time involved you're better off doing a half-open range query, such as:

    ... WHERE OrderDateTime >= @t1 AND OrderDateTime < @t2
    

    For example, if @t1 were the start of today, @t2 would be the start of tomorrow.

Regarding the scenario discussed in comments where the user's time zone has changed:

  • If you choose to calculate the local date in the database, the only scenario you need to worry about is if a location or business switches time zones without a "zone split" occurring. A zone split is when a new time zone identifier is introduced which covers the area that changed, including their old and new rules.

    For example, the latest zone added to the IANA tzdb at the time of writing this is America/Punta_Arenas, which was a zone split when the southern part of Chile decided to stay at UTC-3 when the rest of Chile (America/Santiago) went back to UTC-4 at the end of DST.

    However, if a minor locality on the border of two time zones decides to change which side they follow, and a zone split wasn't warranted, then you'd potentially be using the rules of their new time zone against their old data.

  • If you store the local date separately (computed in the application, not the DB), then you'll have no problems. The user changes their time zone to the new one, all old data is still intact, and new data is stored with the new time zone.

Sunday, June 27, 2021
 
3

See this question. It's not doable in the general case, but for picking a default timezone from a shortlist of likely cases, you can make a decent guess.

Allow the user to override it for when you guess wrong.

Monday, August 2, 2021
 
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :