TW Blog :: Entries for MySQL
The soul is the mirror of an indestructible universeGottfried Wilhelm Leibniz
Using MySQL Variables
Also relates to Databases
Here is a code snippet demonstrating the use of MySQL variables in a set of queries. The problem was to find the longest standing entry in a table for a custom CMS I am building. The date added is a timestamp datatype.
SELECT @min := MIN(added) FROM tbldirectory;
SELECT @interval := UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(added),
@name := name
FROM tbldirectory WHERE added = @min;
SELECT @weeks := MOD(FLOOR(@interval / (7 * 24 * 60 * 60)),52) as weeks,
@years:= FLOOR(@interval / (7 * 24 * 60 * 60 * 52)) as years,
CONCAT(@years, ' year', IF (@years!=1,'s',''), ' and ',
@weeks, ' week', IF (@weeks!=1,'s','')) as duration,
@name as name;
I wanted to display the length of time in years and weeks, so once a variable
(@interval) was set to the duration in seconds, I used some
basic arithmetic with the FLOOR and MOD functions to get
the appropriate values. To keep the final select query readable I stored the week and year calculations in
variables and then used the CONCAT function to build the required string.
Using the variables and functions in MySQL queries is a great way to reduce unnecessary formatting of the data within the API, and now the query result is ready to go straight into the template. The MySQL Control Centre has a useful SQL editor for building complex query strings step by step.
Posted on Nov 10, 2003 at 02:22:21. [Comments for Using MySQL Variables- 0]
MySQL Date Datatypes
Also relates to Databases
Here is a good tutorial on Date Arithmetic with MySQL. It demonstrates a small part of the depth of the Date API in MySQL which can dramtically cut down on uneccessary user defined date functions in PHP and the like. Simon Willison has also written a brief follow up to this on Storing Dates in MySQL and the MySQL Unix conversion functions, which allow a DateTime datatype to be readily converted to the native Unix timestamp format for PHP processing.
This has got me thinking briefly about the best choice for MySQL storage between Timestamp and DateTime. For this blog, I have used the Timestamp datatype in my schemas for all date fields. Both datatypes have access to the full set of date manipulation functions in the MySQL API, and there is no difference in memory use. So, example, for the dates on this blog I have the following call as part of a MySQL SELECT statements:
SELECT […] DATE_FORMAT(added, '%e %M %Y - %T') as create_time FROM […].
- Timestamp can be a useful tool for write once data where a log time is required. The field can be excluded from a MySQL DML UPDATE statement, and will be autocompleted as the statement is executed.
- Where a schema definition has both an added and amended field, placing the amended field first will ensure it is automatically updated each time a DML statement is executed. However, when the data is first entered, the added field must be set specifically using NOW() or a NULL value.
- A Timestamp value can also be explicitly set in an INSERT or UPDATE statement.
- For a schema with a predefined date range of historical significance, the Date or DateTime datatype is a more reliable choice - see this comment on Simon's blog.
- A Date datatype is clearly a better choice for a fixed date, eg an appointment or anniversary.
Posted on Jul 13, 2003 at 22:54:59. [Comments for MySQL Date Datatypes- 2]
