\c blogs
-- Movable Type

select
	coalesce(entry_atom_id,entry_basename) as id,
	'http://blog.rot13.org/' || date_part('year', entry_authored_on) || '/' || lpad(date_part('month', entry_authored_on)::text,2,'0') || '/' || entry_basename || '.html' as uri,
	entry_title as label,
	entry_authored_on as date,
	case
		when entry_modified_on < ( entry_authored_on + '30 days' ) then entry_modified_on
		else entry_authored_on + '1 day'
	end as modified,
	author_email as author,
	entry_text as content,
	date_part('year', entry_authored_on) as year,
	length(entry_text) as length_valueType_number
from mt_entry
join mt_author on entry_author_id = author_id
order by entry_authored_on desc
;
