SQLite Date Values in DVDPedia

[drjohncmac]

Is the Julian Date used as the date format for date fields in the DVDPedia SQLite database?

If so, is the reference date 1 Jan 1950?

If not, how does one decode the "raw" values back into valid dates?

Thanks for your help

[Conor]

Apple now uses January 2001, GMT as the reference date for the epoch. To verify I entered (January 18 2008 0:00 GMT) into a blank entry and looked it up with sqlite3 and got 222307199.999986776.

Code: Select all

222307199.999986776 seconds
3705119.9999997796 minutes
61751.999999996327 hours
2572.999999999847 days
7.049315068493 years
365 * .049315068493 = 17 days and 23:59:59.59 hours

7 years and 18 days from the beginning of 2001.
222307199.999986776 = January 18 2008 0:00 GMT

I can't find an online tool to convert from seconds to date (they are all based on the UNIX 1970 reference date) but I did find one that will let you go from a date to seconds. There are also date functions you can use directly in sqlite3.

[drjohncmac]

Thanks Conor ... that did it.

The algorithm to convert the decimal values into the current year (which I need for my purposes) is

INT(2001 + [field] / 31536000)

INT being the function which returns the integer part of a number.

I'm integrating php and the DVDpedia SQLite database using IIS and Microsoft Vista.

If you'd like, I'll let you know the success of this, and the steps involved.

Once again, thanks for your help

John C

[Conor]

Sounds interesting, I hope it all integrates well. Of course we would love to hear about it, DVDpedia has been integrated with so many programs and work flows it's always fascinating to know how it gets extended; it also gives us feedback on what users want from DVDpedia in the future.

[drjohncmac]

Wanted to give you an update ... the solution is surprisingly easy.

Just find the number of seconds between the Apple epoch of 1/1/2001 into the Unix epoch of 1/1/1970, which is 978307200 seconds.

The sprintf function of SQLite will then return the correct value using the syntax

strftime("%m/%d/%Y", date_field+978307200, 'unixepoch')

Some of the date_fields match IMDB entries exactly, some are off by a couple of days or even months (haven't found any year differences yet). This could be due to the original source of the materials. I updated from a copy of DVDProfiler.

However, all looks good for my purposes.

Integration of php with SQlite is a "snap" ... there is an ODBC driver for SQLite, which I'm using with php. I'll send screen shots of the site once it's a little more developed.

Once again, thanks for all your help.