What is the date format when exporting database to .csv file?

wonshu
wonshu
Community Member
in Mac

Hi there,

I would like to (and did) export my database to a .csv text file.

How can I turn the "date created" and "date modified" columns into human readable format?

Thanks and kind regards,
Hans

1Password Version: 6.8.1
Extension Version: Not Provided
OS Version: 10.11
Sync Type: Not Provided

Comments

  • MrC
    MrC
    Volunteer Moderator

    Hi @wonshu ,

    These are "Epoch" times, seconds from 1970. There is no trivial formula to convert these time stamps, because they rely on leap year, locale, and time change information.

    Let me suggest you use the onepif converter with the csv formatter in the converter suite of utilities. Use the 1.10 version in Testing Bits, mentioned in the first post.

    With it, you can export your 1PIF file into CSV, which will contain the converted time stamps.

    Example 1PIF item (which you export from 1Password):

    {"uuid":"4A27DCAF7FB34FE89B09775A96FA95FB","updatedAt":1400347201,"securityLevel":"SL5","openContents":{"tags":["STOCK"]},"contentsHash":"d1a206bc","title":"Bank Account - stock","secureContents":{"accountNo":"2222222","branchPhone":"555-1212","bankName":"my bank","owner":"joe bank user","routingNo":"1111111","swift":"9998174","iban":"66666","sections":[{"fields":[{"k":"string","n":"bankName","v":"my bank","t":"bank name"},{"k":"string","n":"owner","v":"joe bank user","t":"name on account"},{"k":"menu","n":"accountType","v":"savings","t":"type"},{"k":"string","n":"routingNo","v":"1111111","t":"routing number"},{"k":"string","n":"accountNo","v":"2222222","t":"account number"},{"k":"string","n":"swift","v":"9998174","t":"SWIFT"},{"k":"string","n":"iban","v":"66666","t":"IBAN"},{"k":"concealed","v":"1234","n":"telephonePin","a":{"generate":"off"},"t":"PIN"}],"title":"","name":""},{"fields":[{"k":"phone","n":"branchPhone","v":"555-1212","t":"phone"},{"k":"string","n":"branchAddress","v":"123 main st. redmond, wa","t":"address"}],"title":"Branch Information","name":"branchInfo"}],"accountType":"savings","notesPlain":"stuff","telephonePin":"1234","branchAddress":"123 main st. redmond, wa"},"createdAt":1400347088,"typeName":"wallet.financial.BankAccountUS"}

    Example converted into CSV:

    Category,Title,"bank name","name on account",type,"routing number","account number",SWIFT,IBAN,PIN,"Branch Information::phone","Branch Information::address",Tags,"Last Updated","Date Created",Notes

    "Bank Account","Bank Account - stock","my bank","joe bank user",savings,1111111,2222222,9998174,66666,1234,555-1212,"123 main st. redmond, wa",STOCK,"2014-05-17 10:20:01","2014-05-17 10:18:08",stuff

    See the README.pdf for details on using the onepif converter, but the conversion command you run will look like something like:

    perl  convert_to_1p4.pl  onepif 1pif_export.1pif  --format csv

    and it will place the CSV on your Desktop with the name 1P_converted.csv.

  • wonshu
    wonshu
    Community Member

    Thank you.

    Is there no way that a spreadsheet program can simply interpret these numbers correctly?

    Otherwise I may have to go down that route...

    Best
    Hans

  • MrC
    MrC
    Volunteer Moderator

    @wonshu,

    The conversion is platform-specific, and I don't know which spreadsheet program you are using. Here's the rough idea, and a Google search will provide others (search: epoch conversion spreadsheet):

    https://stackoverflow.com/questions/15164237/how-do-i-convert-a-unix-epoch-timestamp-into-a-human-readable-date-time-in-excel

    See if this helps you any.

This discussion has been closed.