Month: June 2016

More Awesome Stuff for You to Click On:

EDC of the Week
EDC of the Week
Spyderco Manix 2XL Glow in the Dark
Bandit Titanium Keychain Knife
Hometown Handmade Knives

This responsibility can be given to MySQL to automatically assign current time values to these columns.

In MySQL 5.6 onwards, this can be done by setting the data type of the columns to be either date time or timestamp and creating date_created column with NOT NULL DEFAULT CURRENT_TIMESTAMP  schema and date_updated column with NOT NULL DEFAULT ‘0000-00-00 00:00:00’ as schema with attribute ON UPDATE CURRENT_TIMESTAMP.

Below is a sample schema of a table containing date_created and date_updated columns:

CREATE TABLE `time_stamp` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_updated` timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’ ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

But there is a catch here, since this is not applicable to all MySQL versions. Earlier to 5.6 version, MySQL allows a table to have only one TIMESTAMP column with an automatic TIMESTAMP value, that is you can either have date_created or date_updated auto updated to CURRENT_TIMESTAMP not both.

Related to this the MySQL documentation has some very good info at this page: http://ift.tt/1wimyw9

Excerpt: "By default, TIMESTAMP columns are NOT NULL, cannot contain NULL values, and assigning NULL assigns the current timestamp."

So the workaround this is to have date_created column with timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’ schema  and date_updated column with timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’ ON UPDATE CURRENT_TIMESTAMP schema.

With above, the date_updated field is correctly updated by MySQL automatically when there is an update and to update date_created field with current timestamp value, we have to explicitly pass NULL value to date_created field which will then store the CURRENT_TIMESTAMP value in the field.

Below is a sample schema for above changes:

CREATE TABLE `time_stamp` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `date_created` timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
 `date_updated` timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’ ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

So for a structure as above the insert query would be as below:

INSERT INTO `test`.`time_stamp`
(`id`, `date_created`) 
VALUES
(NULL, NULL);

For update the query would be:

UPDATE 
    `test`.`time_stamp` 
SET 
    `id` = ‘4’ 
WHERE 
    `time_stamp`.`id` = 1;

Above will just update the date_updated with the CURRENT_TIMESTAMP and the date_created value will remain same as earlier. Caution needs to be taken while updating records so as not update date_created field with NULL value.

One could also say that instead of MySQL automatically updating the date_updated field, I would like the date_created field to be updated automatically by it. Yes, it is just a reverse case and can be used, in which case while updating the record one has to pass NULL value to date_updated field. But the thing is that since there will be many updates and only a one time insert so passing a NULL while a one time insert reduces the overhead on us of passing a NULL for every subsequent update and let MySQL handle it for us. But yes this is debatable and there are cases where this reverse structure can be used.

Another workaround this is to have date_created field updated with current timestamp value using  a trigger when a record is inserted.

Hope this helps 🙂

(credit: Marco Arment)

With the rise of standing desks, office workers hope to brush off the health risks linked to prolonged sitting, including obesity, cardiovascular disease, diabetes, and early death. But they might do well to walk them off instead, a new study suggests.

In one of the few studies to carefully count the calories people burn while sitting at a desk, standing, or taking a leisurely stroll, researchers found little difference between being plopped down or upright. Standing for an hour might burn off an extra nine calories or so, about the amount in a single gummy bear. Slow walking, on the other hand, incinerated 2.4 to 2.7-fold more calories than standing or sitting, respectively. If office workers fit in an hour of strolling throughout each day—tallying trips to the bathroom, walks to the printer, or strides on a treadmill desk—they could easily burn through an extra 130 calories. That’s a little more than what previous research suggests could help people keep pounds off, the authors report in the Journal of Physical Activity and Health.

“If you’re looking for weight control or just solely at the energy expenditure, standing isn’t that much more beneficial than sitting,” Seth Creasy, an exercise physiologist at the University of Pittsburgh and lead author of the study, told Ars. Of course, calorie burning isn’t the only reason people might choose a standing desk. Being upright could be beneficial for productivity or posture, Creasy said. However, more research is needed to know if those benefits are real because the studies that have been done so far have come up with mixed or inconclusive results.