PRS-T2 Lost 'date added' after a calibre transfer

[Gozer404]

With version 1.3 of calibre that I was using for managing my PRS-T2, during a transfer I got a problem with the USB link. After restarting the device, all dates inside the PRS-T2 were set to the current date (BTW I also lost the read/unread flag!)

Is is possible to update this date (date the epub was added - sorry I don't know the exact words in the Sony interface, because mine is in french) to be in sync with the Calibre library date ? A plugin somewhere ?

[Gozer404]

No one on the forum ?

[ebmr]

Originally Posted by Gozer404

Is is possible to update this date (date the epub was added

Yes, by directly changing it in the books.db database using SQLite.

But what date do you want to set it to? Do you you remember all the dates of your books?

[Gozer404]

Yes, the idea is to use the same date as in the calibre database. In general I enter some books in calibre, do some tweaking on the tags/covers and copy to the Sony the same day.

Now I imagine that I can use some SQL magic to update the sony database based on the calibre one, but I'm far from having the knowledge !

I had some tries with cygwin's sqlite3 and SQLite expert, but I really don't know how to do it.

[ebmr]

Well, the single steps for a distinct book named book.epub are:

• Get the date for book.epub, let's say 2010-05-01 14:10:00.
• Convert the date into seconds since 1970-01-01 00:00:00 UTC by (e.g.)

  date --date="2010-05-01 14:10:00" +"%s"

• Add 000 to the result and get 1272715800000.
• Perform SQL on books.db:

  update books set added_date=1272715800000 where file_name=book.epub;

Try to make that a batch and backup your database before.

[Gozer404]

Fine thanks. Based on this I created a bash script for cygwin:
I did'nt debug all error cases, in particular some grep results are not correctly filtered

#!/bin/bash

# Create book.txt from sony reader database: sqlite3.exe books.db 'select author,title,added_date from books' > book.txt
# Create reference calibre.txt from calibre database: sqlite3.exe  metadata.db 'select author_sort,title,timestamp from books' > calibre.txt
#
#

N=0
cat book.txt | while read LINE ; do
        N=$((N+1))
        echo "Line $N = $LINE"
        FOUND=""
        BTITLE=`echo "$LINE" | awk -F'|' '{print $2}'`
        BDAT=`echo "$LINE" | awk -F'|' '{print $3}'`
        # Search title in calibre file
        FOUND=`grep "$BTITLE" calibre.txt`
        if [ $? -eq 0 ]
        then
                # Create date based on calibre date
                CDAT1=`echo "$FOUND" | awk -F'|' '{print $3}'`
                CTITLE=`echo "$FOUND" | awk -F'|' '{print $2}'`
                CDAT2=`date --date="$CDAT1" +"%s"`
                CDAT="${CDAT2}000"
                if [ x"${BDAT}" != x"${CDAT}" ]
                then
                        echo "Line $N = Change \"$BTITLE\" status date to ${CDAT}"
                        # Update sony datebase with this date
                        sqlite3 books.db "update books set added_date=$CDAT where title=\"$CTITLE\";"
                fi
        fi
done