Transfer data to new custom date field [DVDPedia]

[yutaro]

Hi, thanks so much for developing and supporting these great Pedia apps, they have been some of my most treasured apps on my mac for the past couple of years.
With the new update I've become very interested in using the new Custom Date fields that have been implemented. I have used Custom 1 as a "First Seen" field for years, and I always wanted a way to sort that column by date, so this new addition of a Custom Timestamp field was very welcomed.
My problem lies in the repetitive task of transferring the data from my Custom 1 field to the new Custom Date field. I saw people had been using sqlite3 via the Terminal to automate tasks such as this, however I cannot find any mention of the new custom fields when browsing Database.pediadata so I have no idea as how to accomplish this.

Basically I have set my Custom 1 field to be the date I first saw a movie, and I want to transfer that information to Custom Date 1. Is this possible?
Btw, the format used in my Custom 1 field is the same as in any other Timestamp field like Date Added and Last Seen.

[Nora]

It's actually not as easy to move data from a regular text field to a date field since one contains a text string and the other a time string which makes them incompatible for MySQL. The easiest thing is probably if you send us your database file and we'll try to do the move for you here.

You'll find the file in your Home folder under ~/Library/Application Support/DVDpedia/Database.dvdpd. Please archive the file before sending it to save space. (Select it and choose 'Archive' or 'Compress' from the File menu. That'll create a new file called 'Database.dvdpd.zip' which you can then attach to the email.) You'll find our email on the support page.

[yutaro]

The new database file I was sent looks perfect! Thank you very much for the help.

[Conor]

You are welcome.

[emery]

Can you give the syntax for transferring the data if the source field *is* a time string field? I can't figure out the syntax for the new custom fields.

[Conor]

Code: Select all

UPDATE zEntry SET zCustomDate1 = strftime('%s', zCustom1) - 978307200;

This is the toughest situation as you're converting between an arbitrary string and a very strict format. The weakness is that the SQLite date functions only understand the standard "2001-01-15" format. So your string dates need to be coerced into the correct date format. It's quite likely you will have to put it through a regular expression text editor first (such as TextWrangler) and do a find and replace.

Code: Select all

select zCustom1, zUID from zEntry;

Dec 02, 2013, 1
Nov 02, 2013, 2
...

Massage the output so that you end up with SQL statements that can be imported.

Code: Select all

UPDATE zEntry SET zCustomDate1 = strftime('%s', "2013-12-02") - 978307200 where zUID = 1;
UPDATE zEntry SET zCustomDate1 = strftime('%s', "2013-11-02") - 978307200 where zUID = 2;
...

The regular expression for the format above would be ([a-zA-Z]*) (\d*), (\d*) replaced for \3-\1-\2. Then you would only need replace Feb for 02 and Nov for 11 and so on for the other months.

978307200 is the number of seconds since 1970, the Mac uses 2001-01-01 as the baseline for dates instead of 1970, hence why it needs to be subtracted in the update function above.

Hope that helps, since the custom date field is empty feel free to experiment, but to be sure to make a backup of your database file before starting.