SQLite Examples

SQLite Examples

Filter to convert date or datetime to Time::Piece object

MySQL

# Time::Piece object to DATETIME format
tp_to_datetime => sub {
    return shift->strftime('%Y-%m-%d %H:%M:%S');
}

# Time::Piece object to DATE format
tp_to_date => sub {
    return shift->strftime('%Y-%m-%d');
},

# DATETIME to Time::Piece object
datetime_to_tp => sub {
    return Time::Piece->strptime(shift, '%Y-%m-%d %H:%M:%S');
}

# DATE to Time::Piece object
date_to_tp => sub {
    return Time::Piece->strptime(shift, '%Y-%m-%d');
}

SQLite

# Time::Piece object to DATETIME format
tp_to_datetime => sub {
    return shift->strftime('%Y-%m-%d %H:%M:%S');
}

# Time::Piece object to DATE format
tp_to_date => sub {
    return shift->strftime('%Y-%m-%d');
},

# DATETIME to Time::Piece object
datetime_to_tp => sub {
    return Time::Piece->strptime(shift, '%Y-%m-%d %H:%M:%S');
}

# DATE to Time::Piece object
date_to_tp => sub {
    return Time::Piece->strptime(shift, '%Y-%m-%d');
}

limit clause

Limit clause has different format every database system. It is good to use same format as the following way.

# {limit COUNT OFFSET}
select * from book {limit 3 0};

# Expanded to the following SQL
select * from book limit 3 offset 0

Register tag processor to use above limit clause.

$dbi->register_tag_processor(
    limit => sub {
        my ($count, $offset) = @_;
        
        my $s = '';
        $s .= "limit $count";
        $s .= " offset $offset" if defined $offset;
        
        return [$s, []];
    }
);