#last modified

How to Add Last Updated Date in WordPress Blog Posts

You might have noticed that some WordPress blogs show a last updated date on the posts or pages. If you’re looking to add the same functionality to your blog too, this plugin will help you to achieve that. Use post updated date plugin and simply show post updated date before content in single post! (more…)

A fairly common requirement for database tables is to have a created and last modified timestamp for each record. This seems to be yet another spot where something fairly straightforward requires you to take the scenic route to implement. Ordinarily, the create table statement would look like:

CREATE TABLE `mydb`.`mytable` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `myfield` VARCHAR(255) NOT NULL , `modified` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE NOW() , `created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE = MyISAM;

And that’s when MySQL threw me a nice

Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.

Upon digging a little further, I found this little nugget in the documentation:

For one TIMESTAMP column in a table, you can assign the current timestamp as the default value and the auto-update value. It is possible to have the current timestamp be the default value for initializing the column, for the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

Well isn’t that just peachy.

The Solution

To overcome this hurdle, I employed the use of a trigger for the created timestamp and decided to leave the modified as is. The create table statement then turned into:

CREATE TABLE `mydb`.`mytable` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `myfield` VARCHAR(255) NOT NULL , `modified` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE NOW() , `created` TIMESTAMP NULL, PRIMARY KEY (`id`) ) ENGINE = MyISAM;

And used the following trigger to process the created timestamp -

USE `mydb`; DELIMITER $$ CREATE TRIGGER trigger_mytable_before_insert BEFORE INSERT ON `mytable` FOR EACH ROW BEGIN SET NEW.created = NOW(); END; $$

Amplify’d from english.aljazeera.net

Libya forces 'open fire' at funeral

At least 15 mourners reportedly killed in eastern city of Benghazi, as anti-government protests continue unabated.

Last Modified: 19 Feb 2011 18:21 GMT

Africa

Read More

Live Blog - Libya

US struggles to respond

Libya leader regrets Ben Ali's fall

Libyans in US allege coercion

Gaddafi's turbulent US relations

Protesters back in Bahrain centre

Anti-government protesters reoccupy Pearl roundabout after troops and police withdraw from protest site in capital.

Last Modified: 19 Feb 2011 14:55 GMT

Middle East

Read More

Live Blog - Bahrain

Bahrain on the brink

US balancing act on Bahrain

Country profile: Bahrain

Algerian police break up protest

Several people are injured and others are arrested as police thwart pro-democracy rally in capital Algiers, reports say.

Last Modified: 19 Feb 2011 17:32 GMT

Africa

Read More

Live blog - Eye on Algeria

Crackdown on protesters

The Arab world at a tipping point?

Winds of change

Eye on Algeria

Taliban attack on Afghan bank

At least 18 dead and more than 70 wounded as suicide bombers storm Kabul Bank in eastern city of Jalalabad.

Last Modified: 19 Feb 2011 17:31 GMT

Central & South Asia

Read More

Modest US gains against Taliban

How Afghanistan became a NATO war

Between haven and hell

Kabul Bank crisis

Protester killed in Yemen clashes

Rival groups clash in capital Sanaa as protests against President Saleh's 32-year rule enter 10th day.

Last Modified: 19 Feb 2011 16:30 GMT

Middle East

Read More

Yemen observes 'Friday of Fury'

Yemen clerics urge unity government

Yemen's religious leaders speak out

Yemenis protest amid crackdown

Libya forces 'open fire' at funeral

Protesters back in Bahrain centre

Algerian police break up protest

Taliban attack on Afghan bank

Protester killed in Yemen clashes

Read more at english.aljazeera.net