Talk In Code

There are numerous ways to print out dates and times, and many hours of programming are spent in converting dates from one format to another.

To do this in MySQL you use the STR_TO_DATE() function, which has two parameters. The first parameter is the time to be parsed and the second is the format of that time. Here is a simple example that converts one date format to a MySQL formatted date string.

SELECT STR_TO_DATE('[21/Apr/2009:07:14:50 +0100]', '[%d/%b/%Y:%H:%i:%S +0100]');

This outputs 2009-04-21 07:14:50.

Using this function is quite intuitive and means that you can convert between time formats very easily. Here are a few more examples of this function.

SELECT STR_TO_DATE('21/04/2009', '%d/%m/%Y'); // 2009-04-21
SELECT STR_TO_DATE('04/21/2009', '%m/%d/%Y'); // 2009-04-21
SELECT STR_TO_DATE('2009-04-21 14', '%Y-%m-%d %H'); // 2009-04-21 14:00:00

If you enter a date that can’t be translated then you will get an error, take the following code that tries to convert a minute value of 87.

SELECT STR_TO_DATE('2009-04-21 14:87', '%Y-%m-%d %H:%i');

The following error message is returned.

Incorrect datetime value: '2009-04-21 14:87' for function str_to_date

This error can be quite easy to reproduce. For example, if you wanted to parse a time that was in 24 format, you would use the %H for hours. Using the %h value for hours will produce exactly this error.

The following table lists all of the different parameters that are involved in the date formatting features within MySQL. Use this to parse your own dates.

Specifier	Description
%a	Abbreviated weekday name (Sun..Sat)
%b	Abbreviated month name (Jan..Dec)
%c	Month, numeric (0..12)
%D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d	Day of the month, numeric (00..31)
%e	Day of the month, numeric (0..31)
%f	Microseconds (000000..999999)
%H	Hour (00..23)
%h	Hour (01..12)
%I	Hour (01..12)
%i	Minutes, numeric (00..59)
%j	Day of year (001..366)
%k	Hour (0..23)
%l	Hour (1..12)
%M	Month name (January..December)
%m	Month, numeric (00..12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00..53), where Sunday is the first day of the week
%u	Week (00..53), where Monday is the first day of the week
%V	Week (01..53), where Sunday is the first day of the week; used with %X
%v	Week (01..53), where Monday is the first day of the week; used with %x
%W	Weekday name (Sunday..Saturday)
%w	Day of the week (0=Sunday..6=Saturday)
%X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric, four digits
%y	Year, numeric (two digits)
%%	A literal % character
%x	x, for any x not listed above

Apache can be set up to log all sorts of information

As of Apache 2.2 the basic log file format that a fresh install of Apache will produce will have the following format:

%h %l %u %t "%r" %>s %b

Which doesn’t mean a lot to the uninitiated, so here is a short explanation of each.

• %h – The remote host. This is the IP address of the user connecting to the server.
• %l – The remote logname. This is not always present.
• %u – The remote user from auth (nothing if authentication is not used).
• %t – The time in a common log format.
• "%r" – The first line of the request, basically the method used (GET/POST) the URL that was accessed and the HTTP protocol level that was used. This is enclosed in quotes.
• %>s – %s returns the status of the original request request. For some requests Apache will internally create a secondary request, so %>s prints out the last request staus.
• %b – This is the number of bytes transmitted to the user.

This would produce the following sort out output.

127.0.0.1 - - [17/Apr/2009:14:12:20 +0100] "GET / HTTP/1.1" 200 515

This information can be converted into a database format by using the LOAD DATA command. First, lets create the table we need to store this log format.

DROP TABLE IF EXISTS `test`.`apachelog`;
CREATE TABLE  `test`.`apachelog` (
  `remote_host` varchar(17) DEFAULT NULL,
  `remote_logname` varchar(45) DEFAULT NULL,
  `remote_user` varchar(45) DEFAULT NULL,
  `time1` varchar(22) DEFAULT NULL,
  `time2` varchar(7) DEFAULT NULL,
  `first_line_of_request` text,
  `last_request_status` varchar(4) DEFAULT NULL,
  `bytes_sent` varchar(10) DEFAULT NULL
)

Here is the command that is used to convert the log file into that table.

LOAD DATA INFILE 'C:/Program Files/Apache Software Foundation/Apache2.2/logs/access.log' INTO TABLE apachelog
FIELDS TERMINATED BY ' ' OPTIONALLY ENCLOSED BY '"' ESCAPED BY ''
LINES TERMINATED BY 'rn';

Note that our table contains two fields for the time. This is because of two factors. The first is that each field is defined by a space, and because the time value contains a space it is split into two fields. The second is that although we say OPTIONALLY ENCLOSED BY '"' to stop the %r output being split apart, we can’t give the LOAD DATA command more than one of these fields. As a result the time is split into the two fields, so we just create a table with more than one field for time to accommodate this.

To improve the table we can use a different output format. Take the following slight alteration to our log file format.

LogFormat "%h,%l,%u,%t,"%r",%>s,%b"

This line can be found in your http.conf file or your httpd-vhosts.conf if you have set up virtual hosts.

This will cause our output to look like the following:

127.0.0.1,-,-,[20/Apr/2009:14:42:01 +0100],"GET / HTTP/1.1",200,515

We can now change out two time columns to a single one, setting the datatype to VARCHAR(30) and using the following LOAD DATA syntax to load our data.

LOAD DATA INFILE 'C:/Program Files/Apache Software Foundation/Apache2.2/logs/access.log' INTO TABLE apachelog
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY ''
LINES TERMINATED BY 'rn';

This gives us a much better set of data in our table.

You can also use the following three MySQL commands to convert from the old table format to the new one.

ALTER TABLE apachelog CHANGE time1 time VARCHAR(40);
UPDATE apachelog SET time = CONCAT(time,' ',time2);
ALTER TABLE apachelog DROP COLUMN time2;

The time2 column will now no longer exist.

A new feature in MySQL version 5.1.6 is the addition of events. These can be either a single event or a schedule, both of which can be given multiple commands to run.

First, you need to make sure that the event scheduler is running. To do this, open up MySQL query browser (or similar) and run the following MySQL command.

SHOW PROCESSLIST;

If the event scheduler you will see a row in the output that looks like this:

Id, User, Host, db, Command, Time, State, Info
120, 'event_scheduler', 'localhost', '', 'Daemon', 242, 'Waiting on empty queue', ''

Turning the event scheduler on and off is quite straightforward and can be done as a MySQL command, or as a parameter when starting the server, or even in an ini file. To turn the scheduler on as a MySQL command run the following:

SET GLOBAL event_scheduler = ON;

Conversely, to turn it off, run the following:

SET GLOBAL event_scheduler = OFF;

To turn it off when you start the MySQL server use the following parameter. Exchange DISABLED with ENABLED to turn it on.

--event-scheduler=DISABLED

To turn it off in the ini file use the following. Exchange DISABLED with ENABLED to turn it on.

event_scheduler=DISABLED

For all instances of controlling the event scheduler you can also use 0 to turn it off and 1 to turn it on instead of DISABLED and ENABLED. You can add and alter events when the scheduler is turned off, but the events will not be run until the scheduler is enabled.

To create an event you need to use the CREATE EVENT command. Lets start off by creating a single event that adds a row to a table called test in the test database at a specified time in the future. The event is called addTimestamp and the command that inserts data is after the DO command. This doesn’t have to be on a separate line, but it looks better and will makes sense when you start adding multiple commands.

CREATE EVENT addTimestamp
 ON SCHEDULE AT '2009-03-30 10:20:00'
 DO
  INSERT INTO test.test(timestamp) VALUES (UNIX_TIMESTAMP());

Note that you need to use

If you create an event in the past you will get the following message.

Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation.

Note that this does not run your command, MySQL will just throw away your event and do nothing. The ON COMPLETION NOT PRESERVE setting tells MySQL to either save the event when it has been run, or to throw it away when complete. To save the event after it has been run add the following line underneath the ON SCHEDULE line.

ON COMPLETION PRESERVE

If you set this and try to create another event of the same name you will get an error stating that the name already exists, even if the old even is in the past.

To insert multiple MySQL commands you need to use the BEGIN and END commands. The following command builds upon the previous example, except this time the table is truncated (cleared) and a new timestamp is added.

delimiter |
 
CREATE EVENT addTimestamp
 ON SCHEDULE AT '2009-03-30 10:28:00'
 DO
  BEGIN
   TRUNCATE TABLE test.test;
   INSERT INTO test.test(timestamp) VALUES (UNIX_TIMESTAMP());
  END |
 
delimiter ;

The delimiter tags tell MySQL to pass the entire block in between BEGIN and END to the server before resetting this to the default and parsing each MySQL command separately.

If you want to run the event in an hour, and don’t want to be tied down to times, then change the ON SCHEDULE line to read the following:

ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR

It is easy enough to see how to change the value of the interval here so I will leave this as an exercise to the reader.

To create an event that occurs every hour you need to change the syntax of the command slightly. Rather than give the SCHEDULE command a time or future interval you use the EVERY command.

delimiter |
 
CREATE EVENT addTimestamp
 ON SCHEDULE
  EVERY 1 HOUR
 DO
  BEGIN
   TRUNCATE TABLE test.test;
   INSERT INTO test.test(timestamp) VALUES (UNIX_TIMESTAMP());
  END |
 
delimiter ;

This can be taken a step further by restricting the window of execution. Lets say we wanted to run a command or set of commands every hour for the next 5 hours. The following commands would be added to the ON SCHEDULE command.

EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
ENDS CURRENT_TIMESTAMP + INTERVAL 5 HOUR

This will cause the commands to be run a total of 5 times.

If you want to know what events or schedules you are currently running the run the MySQL command SHOW EVENTS, this will give you a table full of information. This table will also contain any events that are in the past, but which have had the ON COMPLETION PRESERVE setting added to their creation.

To drop an event you need to run the DROP EVENT command, followed by the name of the event you created.

DROP EVENT addTimestamp;

For more information on the use of the CREATE EVENT syntax please see the MySQL manual page on the subject. There is also more information available on the event scheduler.

Sometimes, especially when moving data from one server to another, you might find that you have encoded your MySQL database incorrectly. This problem with first show itself if you have the database encoded in one charset and your website set to display in another. If this is the case then you will find strange characters appearing in your text, especially when using punctuation marks. If you are unable or unwilling to change the character encoding on the site then you need to change how the data is encoded in the database.

The most common sort of thing you might want to do is change from iso-8859-1 (or windows-1252) to UTF-8. This can be done in one of two ways.

The first way is to simply alter the table so that the column contains a different charset.

ALTER TABLE table MODIFY col1 VARCHAR(50) CHARACTER SET 'utf8';

However, if your database has already been set up and your data has already been inserted in the wrong format then you can also update the data in the column using the CONVERT command. The following snippet turns our latin1 data into uncoded binary data and then into utf8.

UPDATE table SET col1=CONVERT(CONVERT(CONVERT(col1 USING 'latin1') USING BINARY) USING 'utf8');

You should also make sure that the connection to the database is done through a specific character set. This is done by using the SET NAMES command and the SET CHARACTER SET.

SET NAMES 'charset_name'
SET CHARACTER SET 'charset_name';

These two commands basically set some values in your MySQL database, for more information on what is set look at the Connection Character Sets and Collations page on the MySQL website. This ensures that the data we get back from the database is also in the correct charset.

For a full list of the different character sets available in MySQL just run the command:

SHOW CHARACTER SET;

This will display a table with the columns Charset, Description, Default collation and Maxlen. Each charset is associated with a collation. A collation is a set of rules for comparing characters in a charset, so it is important that you get this right if you want the database to work. The full list of collations can be viewed using the following command:

SHOW COLLATION;

You can even use a LIKE statement to refine the collation data into the information you are looking for.

SHOW COLLATION WHERE Charset LIKE '%utf%'

Run the following table definition to create a table called tests in the test database.

DROP TABLE IF EXISTS test.tests;
CREATE TABLE test.tests (
 id int(10) unsigned NOT NULL auto_increment,
 var varchar(45) NOT NULL,
 PRIMARY KEY (id)
);

Fill it with some default values:

INSERT INTO tests(var) VALUES('one'), ('two'), ('three'), ('four');

Now lets say that you want to find the values in the table, but that you want to increment a value for each row, the following procedure will do this. If you are using the MySQL Query browser then open a script table and past the following and execute, changing any database/table definitions.

DELIMITER $$
 
DROP PROCEDURE IF EXISTS `test`.`Increment` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Increment`( number CHAR(64) )
BEGIN
 
SET @total=0;
SELECT id, @total:=@total+number AS RunningSum FROM tests;
 
END $$
 
DELIMITER ;

You will now be able to call the procedure in the following way:

CALL Increment(4);

The value that you pass to the procedure is used to work out the increment. So in this case the output would be something like the following:

id  RunningSum
1   4
2   8
3   12
4   16