Query W3C Date

#1 / May 06, 2010 1:31am

[/root.]

29 posts

Hi,

I’m trying to get a valid W3C date for google sitemaps. I’m using a custom query instead of “normal” exp:weblog:entries tag (this way I don’t get error messaged because of thousands of entries).

So, here is my code:

{exp:query sql="SELECT    exp_weblog_titles.title as title,
                        DATE_FORMAT(FROM_UNIXTIME(exp_weblog_titles.edit_date), '%Y-%m-%dT%H:%i:%s%Q') as date_formatted,
                        exp_weblog_titles.entry_id as entry_id,
                        exp_weblog_titles.url_title as url_title,
                        exp_weblog_data.field_id_67 as iata
                FROM exp_weblog_titles
                LEFT JOIN exp_weblog_data
                ON exp_weblog_titles.entry_id = exp_weblog_data.entry_id
                WHERE exp_weblog_titles.weblog_id = '24'"}
    <url>
        <loc>{homepage}airports/{iata}</loc>
        <lastmod>{date_formatted}</lastmod>
        <changefreq>daily</changefreq>
        0.7</priority>
    </url>
{/exp:query}

unfortunately this don’t work i.e. {date_formatted} is not processing and I get {date_formatted} as a raw text instead of a query result. Please advise, thanks.

#2 / May 06, 2010 10:29am

ender

1644 posts

I usually do my sitemaps using PHP for the better date/time processing, though this is probably doable with a SQL date formatting function if you wanted to badly enough.

<?php
    global $DB, $PREFS;
    
    $blog = $DB->query("SELECT url_title, edit_date FROM exp_weblog_titles 
                        WHERE weblog_id='1' AND status='open' ORDER BY edit_date DESC");
?>

<?xml version="1.0" encoding="UTF-8"?>
<urlset ...>
    <?php
        if($blog->num_rows > 0) {
            foreach($blog->result AS $details) {
                print "<url>\n\t\t";
                print "<loc>{$PREFS->ini('site_url')}blog/details/{$details['url_title']}/</loc>\n\t\t";
                print "0.7</priority>\n\t\t";
                print "<lastmod>" . date("c", strtotime($details['edit_date'])) . "</lastmod>\n\t";
                print "</url>\n\t";
            }
        }
    ?>
</urlset>

#3 / May 06, 2010 12:46pm

[/root.]
29 posts

ender,

Thanks, your example works great and seems I’ll stick with it.

However would anyone tell me how to do the same but using query approach only. Just to know 😊
#4 / May 06, 2010 1:31pm

ender
1644 posts

the issue is that edit_date isn’t stored as a unix timestamp like everything else. It looks like this: 20090106163419 and is stored in a bigint(14) field. I’m really not sure why… presumably they had their reasons. Hopefully good ones even 😉

looks like mysql’s date_format function can handle this format as the date input even though it isn’t held in a datetime or timestamp field, so you could probably just drop the FROM_UNIXTIME() function from your original query.

Thread

[/root.]

ender

[/root.]

ender

Username

Password

Thread

Query W3C Date

[/root.]

ender

[/root.]

ender

ExpressionEngine News!

Username

Password

Email Address

Display Name

Password