Why does exp_weblog_titles have a year, month, and day field?
Development and Programming
It looks to me like you’re storing the entry_date and expiration_date as unix timestamps. So I’m a little puzzled about the existence of year, month, and day fields as well, since it seems like you could always get that information from the timestamp.
Oh.
Wait, I bet it’s for performance, isn’t it, so that user templates can select events by year, and that can become a straight SQL query on your ‘year’ field, rather than computing two timestamps and doing a BETWEEN query.
Anyway, the reason I ask is that I’d made two custom date fields on a weblog, thinking I could use those with the exp:calendar tag. But I can’t; it appears that exp:calendar only operates on the entry_ and expiration_date fields. So I need to copy the values of my two custom date fields into the entry_date and expiration_date fields, and I wasn’t sure if I also needed to reset the year, month, and day fields when I do so. I now assume I do. Let me know if I’m wrong? 😊
Thanks! Ben
What I ended up doing was
select entry_id, field_id_21, field_id_22 from exp_weblog_data where site_id=3 and weblog_id=8then taking that export and running it through TextMate, replacing
(\d+)\s+(\d+)\s+(\d+)with
update exp_weblog_titles set entry_date=$2, expiration_date=$3,
year=YEAR(FROM_UNIXTIME($2)), month=MONTH(FROM_UNIXTIME($2)),
day=DAY(FROM_UNIXTIME($2)) where entry_id=$1;and then running the resulting SQL file. Seems to have done the trick nicely.