What's new

I'm stumped on this one ....

freddofrog

won 16.4k on Euro lottery :)
Messages
7,326
Reaction score
847
Location
Off abroad
Car
TBD
As you may know, Microsoft Excel uses a number-code to store dates and times

e.g. Monday 5th June 2017 16:40

In Excel this is stored as 42891.6805556

which is 42,891 days since 1st January 1900
and 58,800 divided by 86,400
(58,800 seconds out of 86,400 seconds in 24 hours).

I am trying to make sense of numbers in a database.

There are two numbers

1496676000000

1497674365000

(for what it's worth, difference is 998365000)

One of those numbers seems to be responsible for Monday 5th June 2017 16:40

Any ideas how either of those numbers will generate the date and time ?
 
Hondata76 said:
whats the source database is it sql?
A "player" , that downloads and plays stuff from a "public broadcasting" company, uses a small SQLite db to store the dates etc.

I found an SQLite ODBC driver ( sqliteodbc.exe from http://www.ch-werner.de/sqliteodbc/ ) which enabled me to link Excel to the SQLite db. There are 7 small tables and those two large numbers are from a couple of columns that, from the column ***les, seem to be ***ociated with the expiry dates of the downloads. The two numbers are for one id for one of the downloads.

All the info in the other columns looks 'kosher' but do you think that those numbers are being incorrectly loaded into Excel ?
 
depends on what the datatype of those columns are if its datetime then yes they are indeed storing date which you can use cast or convert function to convert it to something meaningful. but if its not datetime datatype, it could be encrypted data which may not be editable.
 
I think that you might have set me in the right direction ;)

I've only ever used SQL to query Oracle and Sybase db's , almost always using SQL scripts written in VBA in Excel .

With this particular SQLite db, up to this point I've only used the Excel Query Wizard to import each table into Excel sheets to take a look.

From what you've said, I've found these two links on SQLite data types and datetime functions


1. http://www.sqlite.org/datatype3.html

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

- TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
- REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
- INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

Without having known of the above, I had been messing about in Excel and suspected that it might be based on milliseconds since 01/01/1970 but I haven't tried it as 1970-01-01.


2. http://www.sqlite.org/lang_datefunc.html

I haven't read through that link thoroughly, but there's plenty of scope to go for !! ;)
I'm thinking that one of those 'modifiers' will solve it, so now I'll have to start writing SQL scripts in VBA to query the columns (in fact there's another column ***led 'epoch' )
 
Well writing the SQL into VBA didn't get far, so I had to look for a freeware SQLite DB Browser with SQL query abilities.

Latest release (R18) of Aqua Data Studio looked ok on a 14 day trial but it wouldn't allow editing of SQL because the licence was evaluation only.
But I did find http://www.sqlitebrowser.org which did the job.

The SQL queries in SQLite are weird, probably because SQLite DB schemas are light-weight.

For example, to query a column 'xyz' from a table 'abc' you'd think that "SELECT abc.xyz FROM xyz " would do it, but it has to be "SELECT abc.'xyz' FROM 'abc' 'abc' " or you can put "SELECT def.'xyz' FROM 'abc' 'def' " .

The second link in #7 was useful. By experimenting in the DB Browser it turns out that the dates are stored as 1000 x "unix time" (which gives milli-seconds since 01/01/1970).
 
If anyone out there on the internet is fed up with trying to keep track of which downloaded TV Programmes are about to expire in BBC iPlayer, there is a solution (BBC iPlayer has a facility for sorting by Date Downloaded but it doesn't work).

The following code will list the TV programmes that you have downloaded, in expiry order, in a spreadsheet (by interrogating the iPlayer SQLite db).

-------------------------------------------

Sub episodes()

Dim mycon As New Connection
Dim rs As Recordset

mycon.Open "DSN=iPlayer"
Set rs = New Recordset

rs.Open "SELECT ep_table.'programme_id', ep_table.'series_title', ep_table.'***le', " & _
"date(ep_table.'release_epoch'/1000,'unixepoch'), time(ep_table.'release_epoch'/1000,'unixepoch'), " & _
"date(ep_table.'scheduled_start'), time(ep_table.'scheduled_start'), " & _
"date(ep_table.'availability_end'), time(ep_table.'availability_end') " & _
"FROM 'programme_episode' 'ep_table' " & _
"ORDER BY `availability_end` ASC", mycon, adOpenForwardOnly, adLockReadOnly, adCmdText

rowe = 0
Do Until rs.EOF Or rowe = 65536
rowe = rowe + 1
Application.StatusBar = rowe
For colm = 1 To 9
ActiveSheet.Cells(rowe, colm).Value = rs.Fields(colm - 1)
Next colm
rs.movenext
Loop
rs.Close
mycon.Close

End Sub


-------------------------------------------

The actual SQL in the VBA is

SELECT ep_table.'programme_id', ep_table.'series_title', ep_table.'***le',
date(ep_table.'release_epoch'/1000,'unixepoch'), time(ep_table.'release_epoch'/1000,'unixepoch'),
date(ep_table.'scheduled_start'), time(ep_table.'scheduled_start'),
date(ep_table.'availability_end'), time(ep_table.'availability_end')
FROM 'programme_episode' 'ep_table'
ORDER BY `availability_end` ASC


-------------------------------------------


The above VBA code needs to go into a VBA Module in Excel and it will load the columns 'programme_id' , 'series_title' , '***le' , 'release_epoch' , 'scheduled_start' , 'availability_end' from table 'programme_episode' sorted by 'availability_end' (which is the expiry date and time) , into a sheet in Excel.

The only thing that needs doing is to set up an ODBC DSN to the iPlayer db (if you've never done it before it's not difficult, but it can seem convoluted if you've never done it before).
 
before I forget, this gives better results ....

SELECT ep_table.'series_title', ep_table.'***le', datetime(lic_table.'licence_end'/1000,'unixepoch')
FROM 'programme_episode' 'ep_table', 'programme_licence' 'lic_table'
WHERE ep_table.'programme_id' = lic_table.'programme_id'
ORDER BY `licence_end` ASC
 
Top