SQL Server: How to truncate a date

Many times we have been encountered with the necessity to  truncate a date to retrieve data from defined periods of time.

With SQL Server 2008 and above, this is simple done executing the query below:

SELECT cast(getDate() AS Date)

For previous versions, the proper way to do it is:

SELECT dateAdd(dd, dateDiff(dd,0, getDate()), 0)

MySQL: Update timestamp field with random dates

Today had to add a random unixtime date to a customer's table. Fortunately, MySql provides the RAND() function as well with native support to unixtime (EPOCH), which makes this a breeze.

The formula we need to use is Rand() * (end - start) + start. To obtain the proper values, you use the sample below.

SELECT UNIX_TIMESTAMP('2014-01-01') AS start, UNIX_TIMESTAMP('2014-12-31') AS end;

Once you have the values you want, use the script below to update the desired field with a random date.

UPDATE video SET
published = (RAND() * (1419980400 - 1388530800) + 1388530800)
WHERE published = 0;

FuelCMS: Filter in a simple module using a join in list items.

FuelCMS provides a simple, yet powerful way to create models. When creating a model with a join to another table, the "_common_query" method should be overridden when extending the "Base_module_model" to create a query that joins both tables, in order to be able to filter data using data from the foreign table.

class Articles_model extends Base_module_model {
 public $filters = array('content', 'author_name'); 
 public $foreign_keys = array('author_id' => 'authors_model');

 function _common_query(){
  $this->db->join('authors', 'authors.id = articles.author_id', 'left');
  $this->db->select('articles.*', FALSE);
  parent::_common_query();
 }
}

In the above code, you are indicating that the model can be filtered by the articles content field, and the authors name field, which is being joined by populating the $foreign_key property.

The _common_query is overridden to create a query that will join both tables, which will give the ability to search items from fields from the external table.

Finally, to look for the articles written by a specific author name, you just need to call the fuel_model helper method.

fuel_model('articles', array('find' => 'all', 'where' => array('author_name' => 'Shakespeare')));