browser history time stamp question

Thanks Alex - I edited my post about 2 minutes ago with the footer to essentially say that. Additional clumns would be untidy, I'll tryand get my head around it - but just popping out for a little while.

Hi Paul,

One approach could be to create a subquery along the lines of

SELECT visits.id, 'CONSTANT_1_NAME' FROM visits WHERE visits.transition & CONSTANT_1_VALUE > 0
UNION
SELECT visits.id, 'CONSTANT_2_NAME' FROM visits WHERE visits.transition & CONSTANT_2_VALUE > 0
UNION
SELECT visits.id, 'CONSTANT_3_NAME' FROM visits WHERE visits.transition & CONSTANT_3_VALUE > 0
etc...

Then join visits with the subquery, GROUP BY visits_id and group-concat what you get back from the subquery?

It's a bit long-winded but might be neater than hard-coded concatenation?

Hi Alex

Finally ended up with this, SQL isn't really one of my stronger skills )

SELECT visits.visit_time,
visits.id,
urls.url,
urls.title,
visits.url AS URLID,
visits.from_visit,
Query2.visit_time AS FromVisitTime,
Query2.url AS FromURL,
Query2.title AS FromTitle,
urls.visit_count,
printf('%08x',visits.transition),
CASE visits.transition & 0xFF
WHEN 0 THEN 'Link'
WHEN 1 THEN 'Typed'
WHEN 2 THEN 'Auto_Bookmark'
WHEN 3 THEN 'Auto_Subframe'
WHEN 4 THEN 'Manual_Subframe'
WHEN 5 THEN 'Generated'
WHEN 6 THEN 'Auto_Toplevel'
WHEN 7 THEN 'Form_Submit'
WHEN 8 THEN 'Reload'
WHEN 9 THEN 'Keyword'
WHEN 20 THEN 'Keyword_Generated'
END AS CoreTransition,
(CASE WHEN visits.transition & 0x00800000 THEN 'Blocked, ' ELSE '' END ||
CASE WHEN visits.transition & 0x01000000 THEN 'Forward_Back, ' ELSE '' END ||
CASE WHEN visits.transition & 0x02000000 THEN 'From_Address_Bar, ' ELSE '' END ||
CASE WHEN visits.transition & 0x04000000 THEN 'Home_Page, ' ELSE '' END ||
CASE WHEN visits.transition & 0x08000000 THEN 'From_API, ' ELSE '' END ||
CASE WHEN visits.transition & 0x10000000 THEN 'Chain_Start, ' ELSE '' END ||
CASE WHEN visits.transition & 0x20000000 THEN 'Chain_end, ' ELSE '' END ||
CASE WHEN visits.transition & 0x40000000 THEN 'Client_Redirect, ' ELSE '' END ||
CASE WHEN visits.transition & 0x80000000 THEN 'Server_Redirect, ' ELSE '' END ||
CASE WHEN visits.transition & 0xC0000000 THEN 'Is_Redirect_Mask, ' ELSE '' END )
AS Qualifiers
FROM visits
LEFT JOIN urls ON visits.url = urls.id
LEFT JOIN (SELECT urls.url,
urls.title,
visits.visit_time,
visits.id
FROM visits
LEFT JOIN urls ON visits.url = urls.id) Query2 ON visits.from_visit = Query2.id
Which looks like this

Paul - that looks like to does the trick, nicely done! D

Thanks for the great info and education everyone. Very informative