Tags: dates

A quick look at using dates in Mysql

By admin on Jul 24, 2009 | In Daily Commute - Standard hints/tips | Send feedback »

The manipulation of dates in sql is often involved, and Mysql is no exception. I'm going to go over some of the more common requirements when playing with dates in Mysql, but advise that a lot more detail is available in the manual (link :-
date_time_functions_link )

The two main data types you'll probably deploy in Mysql (although there are others) are DATE and DATETIME. If you need to record time of day as well as the date then you'll probably be using DATETIME. If you then want to select records for a certain day accessing a DATETIME field then the function DATE can be very useful:-

Code:

mysql> select dateposted from entries where date(dateposted) = '2008-04-14';

Remember always that when inserting (or selecting from) Mysql DATE fields that the default format is 'YYYY-MM-DD'; when importing from a csv file created by Excel remember to first change the format accordingly on the initial Excel field. Another point (which upsets sql purists) is that if the date value being inserted into a DATETIME field is invalid, often the date will be set to '0000-00-00', rather than the insert being rejected. Here's what the mysql manual used to say on the subject:

The MySQL server only performs basic checking on the validity of a date: days 00-31, months 00-12, years 1000-9999. Any date not within this range will revert to 0000-00-00. Please note that this still allows you to store invalid dates such as 2002-04-31. It allows web applications to store data from a form without further checking. To ensure a date is valid, perform a check in your application.

To show the year part of the date use YEAR(

), to show the month (in digits) use MONTH(), and for the day you can use either DAY() or DAYNAME()

select curdate() will return the current date in yyyy-mm-dd format whereas
select now() will return the current date in a longer datetime format.

For various possibilities in formatting the date output, you can use DATE_FORMAT, for example:

Code:

mysql> select DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
+------------------------------------------------+
| DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y') |
+------------------------------------------------+
| Sunday October 2009                            |
+------------------------------------------------+

Other options include %m (month numeric), %b (abbreviated month name), %e (day of the month numeric), %D (day of month with suffix 1st, 2nd etc). Here's another sql example of DATE_FORMAT:

Code:

mysql> select DATE_FORMAT('2009-10-04 22:23:00', '%e %b %y');
+------------------------------------------------+
| DATE_FORMAT('2009-10-04 22:23:00', '%e %b %y') |
+------------------------------------------------+
| 4 Oct 09                                       |
+------------------------------------------------+

The DATE_FORMAT function can be very useful for finding the first day of a month eg:

Code:

SELECT DATE_FORMAT('2004-01-20' ,'%Y-%m-01');

Once you have this, you can find out the last day of the month, first day of following month etc by using DATE_ADD and DATE_SUB (see below).

To find out the date 50 days from now, use the ADDDATE function, normally using format ADDDATE( date , Number_of_days ), as follows:

Code:

mysql> select curdate(), adddate(curdate(), 50);
+------------+------------------------+
| curdate()  | adddate(curdate(), 50) |
+------------+------------------------+
| 2009-07-21 | 2009-09-09             |
+------------+------------------------+

You can use PERIOD_DIFF (p1, p2) to find the number of months between 2 dates p1 and p2, so long as p1 and p2 are in format YYMM or YYYYMM.

Code:

mysql> select period_diff(200907,200901);
+----------------------------+
| period_diff(200907,200901) |
+----------------------------+
|                          6 |
+----------------------------+

select period_diff(date_format(now(), '%Y%m'), date_format(hiredate, '%Y%m')) as months from emp;

It has to be said however, that the normal method of date arithmetics in Mysql is done with the functions date_sub and date_add.

Code:

mysql> select date_sub('2005-01-01 00:05:00', INTERVAL 1 DAY);
+-------------------------------------------------+
| date_sub('2005-01-01 00:05:00', INTERVAL 1 DAY) |
+-------------------------------------------------+
| 2004-12-31 00:05:00                             |
+-------------------------------------------------+

Code:

mysql> select DATE_ADD('2010-12-31 23:58:00', INTERVAL 1 DAY);
+-------------------------------------------------+
| DATE_ADD('2010-12-31 23:58:00', INTERVAL 1 DAY) |
+-------------------------------------------------+
| 2011-01-01 23:58:00                             |
+-------------------------------------------------+
Tags: dates, datetime, excel

Bumper tricks

By admin on Jul 2, 2009 | In Daily Commute - Standard hints/tips, Formula 1 - More advanced stuff | Send feedback »

I'm going to list on this thread, those little tricks and tips that I've come across over time that really help when using Mysql.
I will probably add to this thread as I find out more.

For example, if you're in command line Mysql, you can show the DDL for a table quite easily:

Code:

show create table table_name;

A useful tip when running sql queries (again in command line mode) is to terminate a query with \G switch instead of a semi-colon. This displays records in row format rather than columns

Normally Mysql queries are case insensitive - if you search for a field containing '%king%', the query will bring back results where the field contains 'King', 'kinG' or even 'KING'
To try and force Mysql to be case sensitive use the 'binary' keyword - for example

Code:

Select mainfield from T1Name where binary mainfield like '%King%'

If you are performing date operations on a datetime field, use the date function, for example:

Code:

select dateposted from entries where date(dateposted) = '2008-04-14' \G

Lastly, if you want to break down the number of orders recorded against different countries into discrete groupings of price, the following code is very useful:

Code:

SELECT 5*FLOOR(productprice/5),
  SUM(CASE WHEN cnty = 'UK' THEN 1 ELSE 0 END) as UK,
  SUM(CASE WHEN cnty = 'FR' THEN 1 ELSE 0 END) as FR
FROM orders o
WHERE o.cnty IN ('UK','FR')
GROUP BY 5*FLOOR(productprice/5);

...More tips to follow soon

... and here's one.
If you want to change the order of a sort (perhaps sort by 1, 4, 3 ,2 instead of 1, 2, 3,4) you can use the field function within the Order by clause.
For example

Order By Field(sort_field,1,4,3,2)

Read more about this extremely useful function HERE Of course you can always use the CASE statement in the ORDER BY clause, but it's always good to have a choice!

Sometimes it's good to know the range of values in your table fields - what the maximum value is, the minimum length stored etc - especially when you're working out what kind of index to implement. In this scenario, the procedure analyse is very useful. In fact it examines the result from a query and returns an analysis of the results. To use it append PROCEDURE ANALYSE to the end of a SELECT statement eg:

Code:

select * from orders procedure analyse(0,0)\G

There's a very useful article on using this when
indexing text columns in mysql here

Tags: command line, dates, tips

A daily quote with php/Mysql

By admin on May 29, 2008 | In Formula 1 - More advanced stuff | Send feedback »

In my other website Other Site I have a daily expression displayed (in German and Spanish). It's fairly easy to do this, although it obviously takes some time in populating the database table with the quotation data.
I have one field as a counter (datatype TINYINT if the value will never exceed 127 otherwise SMALLINT ), this field is called day_seq in my table. In my table I've entered in 31 entries (one for every potential day of the month), although you can obviously enter in more - perhaps 62 (for a unique entry every day over 2 months) or even 365 (for every day of the year). Although not necessary if you only want to access a quote in one category, I also have a field cat_id that determines which language expression I'm pulling back ('spanish' or 'german). So if the day was the fifth of the month and I was pulling back the Spanish expression, my code would be as follows:

Code:

mysql> select lingo, english, cat_id, day_seq
    -> from dichos
    -> where cat_id = 'spanish'
    -> and day_seq = 5;
+---------------------------+----------------------------+---------+---------+
| lingo                     | english                    | cat_id  | day_seq |
+---------------------------+----------------------------+---------+---------+
| Más vale maña que fuerza. | Brain is better than brawn | spanish |       5 |
+---------------------------+----------------------------+---------+---------+
1 row in set (0.01 sec)

Now, in order to pass the parameter (in the example above it's 5, but it changes each day) to the sql statement in php we need to do 2 things. The first is to use the php function getdate() which brings in the current date's details. We then extract the day number string from this date. Here's the code in php to get a new quotation in Spanish every day:

Code:

$today = getdate();
  $mday = $today['mday'];
 
$sql = "SELECT id, lingo, english, cat_id, day_seq  FROM dichos
        WHERE day_seq = " . $mday . "
        AND cat_id = 'spanish'
        LIMIT 1;";
$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);


Note that $today['month'] will bring you back the alpha month (eg 'May' or 'June') once you have populated $today with getdate(). If you want to have more than 1 month's expressions in your table, you can keep incrementing the day_seq value. So, when the month = 'January' you perform the standard join on day_seq = $mday; when the month = 'February you add 31 to $mday (day_seq values in your table of 32 to 59 for this month) and so on. Alternatively you can add a monthseq field to your table.

One further point - if recording foreign text with accents in your database, make sure the collation has been set to UTF8_unicode_ci; unfortunately many versions of Mysql default to a Latin1_Swedish_ci collation. All too easy to have the correct characters replaced by strange icons.

Tags: collation, dates, php