Sqlite Database and Theatrical Date

Any trouble you encounter with the Pedias, here's the place to ask for help.
Post Reply
DreamStatic
Addicted to Bruji
Addicted to Bruji
Posts: 71
Joined: Tue May 30, 2006 4:57 pm

Sqlite Database and Theatrical Date

Post by 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.
User avatar
Conor
Top Dog
Posts: 5337
Joined: Sat Jul 03, 2004 12:58 pm
Contact:

Re: Sqlite Database and Theatrical Date

Post by 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
Addicted to Bruji
Addicted to Bruji
Posts: 71
Joined: Tue May 30, 2006 4:57 pm

Re: Sqlite Database and Theatrical Date

Post by DreamStatic »

As I have always said -- YOU ROCK!!!! Thank you so much. :)
User avatar
Alex
Addicted to Bruji
Addicted to Bruji
Posts: 230
Joined: Sun Aug 08, 2004 4:02 am
Location: Barcelona
Contact:

Re: Sqlite Database and Theatrical Date

Post by 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 :D

Cheers.
User avatar
FineWine
Site Admin
Posts: 852
Joined: Wed May 28, 2008 2:41 am
Location: Tauranga, New Zealand

Re: Sqlite Database and Theatrical Date

Post by FineWine »

Thanks Alex, very well explained for us non geeks, give the teacher an 
User avatar
Alex
Addicted to Bruji
Addicted to Bruji
Posts: 230
Joined: Sun Aug 08, 2004 4:02 am
Location: Barcelona
Contact:

Re: Sqlite Database and Theatrical Date

Post by Alex »

Ha, ha. Love the apple!
Post Reply