Sqlite Database and Theatrical Date

[DreamStatic]

I am attempting to compare an outside source year to DVDpedia's Theatrical date year. For example, say I have the year 2000 from the outside source and want to write a sqlite query that would allow me to check if ZTHEATRICAL is equal to this outside source year 2000? I notice in the database it is a long series of numbers so you couldn't just compare ZTHEATRICAL='2000'. Do you know how to compare just the year from ZTHEATRICAL? Thanks for the help.

[Conor]

The year is stored as a number of seconds since Jan 1st 2001. To translate it into a year use the formula (2001 + ZTHEATRICAL/31536000) == '2000'.

Sqlite automatically does integer math, so the above and below will give you a perfect year and not 2001.5.

Code: Select all

select 2001 + ztheatrical/31536000, ztitle from zentry;

Hope that helps.

[DreamStatic]

As I have always said -- YOU ROCK!!!! Thank you so much.

[Alex]

To understand why that formula works, we need to know that most all languages and platforms out there use what is called the Unix Epoch to calculate dates. The Unix Epoch is a date associated with the creation of Unix (might not have been the actual invention or release date, but you gotta settle on one standard measurement for comparison, you can't use a moving target) which is January 1st 1970.

Rather than storing dates in an arbitrary format (do you use European format of day/month/year or US format of month/day/year? or something else?) languages use seconds which are just numbers and much easier to work with, then translate those.

Of course, Apple is different They don't use the Unix Epoch, they use January 1st 2001 which is 31 years after the Unix Epoch. Why that year? I have no idea, no doubt Apple felt it was silly to go so far back in time and maybe 2001 was a good year for them. But you still use the Unix Epoch for date calculations, so this:

Code: Select all

2001 + ztheatrical/315360000

means the year

Code: Select all

2001

+ your

Code: Select all

theatrical

date divided by

Code: Select all

31,536,000

which are the number of seconds in 1 year.

I know this probably doesn't help you any more than just the code, but I couldn't resist pointing it out for us geeks

Cheers.

[FineWine]

Thanks Alex, very well explained for us non geeks, give the teacher an 

[Alex]

Ha, ha. Love the apple!