Sqlite Database and Theatrical Date

Any trouble you encounter with the Pedias, here's the place to ask for help.

Sqlite Database and Theatrical Date

Postby DreamStatic » Fri Dec 19, 2014 12:47 pm

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

Re: Sqlite Database and Theatrical Date

Postby Conor » Sat Dec 20, 2014 6:13 pm

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

Re: Sqlite Database and Theatrical Date

Postby DreamStatic » Sat Dec 20, 2014 8:22 pm

As I have always said -- YOU ROCK!!!! Thank you so much. :)
DreamStatic
Addicted to Bruji
Addicted to Bruji
 
Posts: 71
Joined: Tue May 30, 2006 4:57 pm

Re: Sqlite Database and Theatrical Date

Postby Alex » Mon Dec 22, 2014 4:24 am

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
Alex
Addicted to Bruji
Addicted to Bruji
 
Posts: 230
Joined: Sun Aug 08, 2004 4:02 am
Location: Barcelona

Re: Sqlite Database and Theatrical Date

Postby FineWine » Mon Dec 22, 2014 4:20 pm

Thanks Alex, very well explained for us non geeks, give the teacher an 
User avatar
FineWine
Addicted to Bruji
Addicted to Bruji
 
Posts: 476
Joined: Wed May 28, 2008 2:41 am
Location: Tauranga, New Zealand

Re: Sqlite Database and Theatrical Date

Postby Alex » Mon Dec 22, 2014 5:31 pm

Ha, ha. Love the apple!
User avatar
Alex
Addicted to Bruji
Addicted to Bruji
 
Posts: 230
Joined: Sun Aug 08, 2004 4:02 am
Location: Barcelona


Return to Support

cron