Followup to "Dates drift in items such as Passport"

TemptinTemptin
edited May 2016 in Mac Beta

Note: Could one of you lovely moderators please put a notice in the old thread that links readers here, just in case Google-people find the old thread instead of this solution?

This is a follow-up to a closed thread, just to help anyone who wanted to know the solution:
https://discussions.agilebits.com/discussion/46583/dates-drift-in-items-such-as-passport

Background story: 1Password used to store all manually entered dates (any field that uses the calendar date-picker menu) using the local UNIX timestamp instead of the UTC UNIX timestamp, which meant that dates could drift if you moved to another timezone (a software license purchase date of "5/2/2015" could become "5/1/2015", etc). People in that closed thread (me included) wanted to know if we could bulk-fix our items in a single go.

The answer is: We can solve it quickly. I was doing database maintenance and discovered this trick today.

  • Step 1: Set your computer timezone to the same one that you used to live in (or still live in) when you created all password items.
  • Step 2: File - Export - All Items. Save the .1pif file somewhere. Since it's an unencrypted storage format, I decided to export my .1pif file onto an encrypted disk image (see this Apple guide for how to make one: https://support.apple.com/en-us/HT201599) so that no plaintext data ever hit my SSD.
  • Step 3: Start over fresh with a brand new database! Do this by disabling syncing, deleting your database from Dropbox/whatever sync solution you use, and finally following the official instructions for nuking your database and all preferences (https://support.1password.com/starting-over/mac.html). Be careful and back it up first!
  • Step 4: With the brand new, empty database, just import your 1pif file. You're done!

The reason this works is because 1Password knows what's a local timestamp and what's a UTC timestamp (they store UTC timestamps as the day timestamp + 12 hours and 1 minute to signify that it's in UTC format). Therefore, the export knows which timestamps are local UNIX time, converts those to UTC time (using your current computer timezone as help), and exports them as UTC timestamps in the 1pif file.

This process makes all timestamps in your database timezone-agnostic, so that you can move anywhere in the world and never have your manually-entered dates drift. They'll stay rock solid. If you don't do this, they will probably drift if you ever move.

When you import the 1pif again, all of your timestamps will be UTC time. Literally all of them, even the "updated/created" times for items (although I think those were always UTC time in the past too, since the "local time" bug only seems to have affected the fields that use the manual OS X date/time calendar picker GUI).

I discovered this quick and painless timestamp solution today when converting from AgileKeychain to OPVault (by using the export/start fresh/import method). I remembered the old timestamp bug and decided to investigate it, and wrote a small script to check what timestamps were stored in the exported 1pif file. They're all UTC timestamps (Example script output: "v = 1460721660 (2016-04-15 12:01:00 UTC)"). The script makes sure every timestamp evaluates to "12:01:00" UTC, and counts that as an OK timestamp; here are my results after the export/import process: "Statistics: 159 checked (159 OK)".

Those who are extra curious can run the script; it expects to be placed inside the .1pif folder, next to the "data.1pif" file, and should be run with "php whateveryoucallthescript.php". It's quick and dirty but did the job. Thanks to this, I can vouch for the fact that the export converts all dates to UTC. Now I can globetrot as much as I want. ;-)

<?php

$data = file_get_contents('data.1pif');
preg_match_all('/"([^"]+)":"?\b(1\d{9})\b[$\S]/', $data, $matches);

$total_checked = 0;
$total_ok = 0;
for( $i=0,$len=count($matches[0]); $i<$len; ++$i )
{
    $type = $matches[1][$i];
    $timestamp = $matches[2][$i];

    if( $type != 'v' ) { continue; } // only look at "v"alue fields holding timestamps

    echo $type . ' = ' . $timestamp . ' (' . gmdate('Y-m-d H:i:s e', $timestamp) . ')' . PHP_EOL;
    $total_checked++;

    // make sure the timestamp is stored with UTC offset
    $test_string = gmdate('H:i:s e', $timestamp);
    if( $test_string == '12:01:00 UTC' ) { $total_ok++; }
}

echo 'Statistics: ' . $total_checked . ' checked (' . $total_ok . ' OK)' . PHP_EOL;

?>

PS: I am using 1Password 6.3.BETA-16, and I cannot check that the export/import converts timestamps to UTC properly with older versions too. But it probably does.

PPS: Exporting and importing is safe. It will maintain all of your tags, folders, smart folders, attachments, custom icons, etc.

PPPS: Another nice benefit of exporting, starting fresh and importing is that you flush out all the "tombstones" from old, deleted database items. Just be sure that you've turned off syncing on all devices and reset them all, so that your old database doesn't try to sync them back into the new one.


1Password Version: 6.3.BETA-16
Extension Version: Not Provided
OS Version: 10.11
Sync Type: Not Provided

Comments

  • TemptinTemptin
    edited May 2016

    Oh and I know this has been a while, but I'll ping the participants from the old thread who wanted this solution:

    @Temptin (hey that's me!), @Locker, @MrC.

  • rudyrudy

    AgileBits Team Member

    @Temptin,

    For what its worth, https://support.1password.com/starting-over/mac.html has gotten simpler in the latest beta (17), Help menu -> Troubleshooting -> Start Over

    Rudy

  • TemptinTemptin
    edited May 2016

    @rudy That's great, thanks for that! Could have saved me 5-10 minutes if I knew that. :)

    PS: Could you or someone else please put a link to this thread from the old one, so that people can find the solution? Here's the old thread: https://discussions.agilebits.com/discussion/46583/dates-drift-in-items-such-as-passport

  • ag_kevinag_kevin Junior Member

    AgileBits Team Member

    Thanks @Temptin. I've put a link in the other thread.

    Cheers,
    Kevin

  • Hello,

    I'm using the 1Password, Version 6.3.3 (633000) from Mac App Store and was recently hit by the same issue. All dates in my passports fields [for all family passports] was moved by one date back. As I'm traveling a lot, solution of exporting and importing data after every trip seems to be too complicated.

    Can you, please, make dates stable?

    With greetings,

    Jaroslav

  • brentybrenty

    AgileBits Team Member

    @medabeda: I'm sorry for the trouble! As mentioned previously, this bug was fixed in version 5.3. The good news is that while items created with an older version will exhibit this behaviour (since they're set to local time), there's no need to export and re-import anything; simply edit the date field to correct it, and save (which will then be set to UTC). I hope this helps! :)

  • @Temptin: Thank you for your post. Very useful information. Somehow I missed your ping to me, but the link from the old thread to this helped.

    I traveled to quite a few places, so I'm not sure where I created various entries. Probably most were at my main time zone. I went manually and fixed wherever I thought I had manually-entered times, but with few thousands of entries I probably missed most of them. (and also had multiple time shifts in the same item. It not just shifted a date, but also created multiple (& shifted) date entries.

    I plan to run your php script at least to see if there are any left overs.

    @brenty: The problem with editing the date fields is to find them :)

  • brentybrenty

    AgileBits Team Member

    The problem with editing the date fields is to find them :)

    @Locker: Ah, a fair point. :blush:

    I traveled to quite a few places, so I'm not sure where I created various entries.

    Just to clarify, it doesn't matter where geographically you were when creating/editing an item, so that's at least one less things to worry about. I think we can take a methodical approach to this. Start with the most important information and go from there. After all, not all items have date fields, or that we care about especially. This is my list:

    1. Passport
    2. Drivers License
    3. Credit Cards
    4. Identities*
    5. Memberships
    6. Reward Programs

    Identities isn't at the top of that list because I won't have any difficulty recognizing if the birthdate is wrong at any point.

    I went manually and fixed wherever I thought I had manually-entered times, but with few thousands of entries I probably missed most of them. (and also had multiple time shifts in the same item.

    I have the most Credit Cards than anything else on this list, and I think altogether I have roughly two dozen items between all of these. Not ideal, but if you have similar (or less) numbers, it at least shouldn't take too long. :chuffed:

  • @brenty: If I understand correctly, you are talking about correcting the date manually when I know whites the correct date and don't rely on the (shifted) date that I see. On the other hand (If I got it right), @Temptin suggests to move to the original timezone so that the local date will show correctly, then export and reimport the whole data so all dates will be rewritten in UTC. (or alternatively, overwrite the displayed date the same way you suggested).

    So the original timezone seems to be important if we are not relying on manual correction when we don't know the correct date. Is it so?

  • brentybrenty

    AgileBits Team Member

    @Locker: Unless I'm misunderstanding what you're suggesting, since 1Password is using UTC dates now, it shouldn't matter. In most cases, all you need to do is edit the item to ensure it reflects the correct date according to your current settings, and then save the item. When you move between time zones after that, the display date may change (across date lines, for example), but the underlying data in 1Password is non-relative and remains the same. Does that help?

  • @brenty: Probably my confusion is: Do all the dates are written in UTC? or only those that were entered or edited after the 1PW version that started to use UTC, and that old entries, if

  • brentybrenty

    AgileBits Team Member

    Probably my confusion is: Do all the dates are written in UTC? or only those that were entered or edited after the 1PW version that started to use UTC, and that old entries, if

    @Locker: Ah, sorry for not being clearer about that. You are correct: as mentioned in the blog post, prior to version 5.3 dates were created with the local time zone setting. In 5.3 and later, all dates are saved as UTC. That's why any items you modify or create now won't be affected by that date bug.

  • My previous post was truncated. Here is the complete post:

    @brenty: Probably my confusion is: Do all the dates are written in UTC? or only those that were entered or edited after the 1PW version that started to use UTC, and that old entries, if still exist, and not rewritten, are still in local time.

    I just now checked my data file using @temptin php and got
    "Statistics: 345 checked (49 OK)"

    While some (49?) look like this:
    v = nnnnnnnnnn (2016-01-27 12:01:00 UTC)

    There are many lines like
    v = nnnnnnnnnn (2011-09-11 04:00:00 UTC)
    v = nnnnnnnnnn (2007-12-27 05:00:00 UTC)
    v = nnnnnnnnnn (2010-06-08 20:40:31 UTC)
    v = nnnnnnnnnn (2010-06-09 07:01:48 UTC)
    v = nnnnnnnnnn (2007-10-27 02:37:01 UTC)
    v = nnnnnnnnnn (2017-10-31 09:35:31 UTC)

    What does it mean? Can I know the time zone it was written in? (perhaps -4 UTC?)

  • ag_kevinag_kevin Junior Member

    AgileBits Team Member

    Hi @Locker,

    To confirm, only entries entered or written after that 1Password version were saved with UTC. It's not really possible to tell what time zone the date was originally written in. It would be whatever timezone your computer was in when you saved the entry.

    Regards,
    Kevin

  • So what is the meaning of a date that was exactly "05:00:00 UTC" for example.

    Is a new date entry is always "12:01:00 UTC" now.

    How an old entry of local timezone looks like? 12am of a local time zone?

  • ag_kevinag_kevin Junior Member

    AgileBits Team Member

    Hi @Locker,

    A time that is shown now as exactly "05:00:00 UTC" with no date was likely an empty date value (0) that was saved back when timezones were taken into account. When saved 5 hours was added to convert to UTC.

    Yes, I believe you are correct. New dates are 12:01:00 UTC, old are likely 12:00am local time zone.

    Regards,
    Kevin

  • @ag_kevin,

    Clarification: When I asked about time "05:00:00 UTC", it had a date too, e.g.:
    v = nnnnnnnnnn (2007-12-27 05:00:00 UTC)
    So was it created at zone -5 UTC (EST, or DST)? (or maybe the other way: +5 UTC, although I don't recall being there)

  • rudyrudy

    AgileBits Team Member

    @locker,

    In that example it should already be in the current date format representing 5am UTC on December 27, 2007.

    Rudy

  • @rudy,
    Yes, but if a date field shows only the day, not the time, wasn't it supposed to pick a time of 00:00:00 in the old local time? (and an artificial 12:01:00 if it was written later on, when the new method was in effect)?

  • rickfillionrickfillion Junior Member

    AgileBits Team Member

    @Locker : no, not quite.

    Let me see if I can shed a bit more light on all of this....

    For one, I see us (AgileBits) saying that in order to update an item, what you need to do as a user is to edit the item then save it out. That's true, but I think it needs to be clarified a little. What you actually need to do is to edit the item, change the date to a new value, change it to the correct value (this may be a single step if the date was off enough), then save the item. Unless the actual date field was modified, the new date format won't be used.

    As for the example of 2007-12-27 05:00:00 UTC, then I'd say that that's a date field that was created in the old format in a timezone that's 5hours off of UTC. 5:00 UTC may be 00:00 in local time, if you're 5 hours off of UTC. But there's no real guarantee of this.

    This stuff is a mess, and I honestly can't recommend that you try to automate the correction of this data. There's a reason that we didn't do an automated fix for it ... doing the correction correctly in all cases considering what we were doing wrong is nearly impossible to do (we weren't able to find a way that we were confident in). You would need to know the timezone that every field was saved in, and that info is just not there. Inferring it is painful, and likely to be wrong in a bunch of cases.

    Rick

Sign In or Register to comment.