Dates and Times in MySQL, Part 2: Methods and Tips

The previous article on the subject gave you a nice introduction to MySQL date types, but here I’m going to go more into entering dates and a few other important points. MySQL has a long list of functions associated with Date and Time fields, and they can be difficult to understand what they do, simply because so many of them do the same thing! Here are descriptions of a few of the more important ones:

NOW( ) – the now function returns the current date and time in the same format as a timestamp. CURRENT_TIMESTAMP( ) is a functional synonym for the now function, along with LOCALTIME( ) and LOCAL_TIMESTAMP( ), and they can be interchanged freely (although I always prefer consistency).
CUDATE( ) and CURTIME( ) return the current date and current time, respectively. It is important to understand that these are different from the previously mentioned set of functions.
Various other functions such as WEEK( ), WEEKDAY( ), and YEARWEEK( ) return the parts of the given date.
There are also a few functions for date arithmetic such as DATE_ADD( ) and DATE_SUB( ) that can be really useful.

More detailed descriptions of these functions can be found here, so you will know exactly how to use them. Now I’m going to show you a really useful example using the NOW( ) function using triggers. I know that a lot of you like to add dates when you insert records into a database. It is a huge pain to have to manually add a date every time you enter a record. Here is some code that I use to automatically enter a timestamp into the date field every time a new insertion is made.

    CREATE TRIGGER insert_datetime
        BEFORE INSERT ON test
        FOR EACH ROW
            SET NEW.date = NOW();

If you don’t know much about triggers, you should take a look a this article, but its not too big of a deal if you just understand this code. If you enter this code into the MySQL command-line, you will create a trigger that will automatically set the date field of every record inserted into test to the current date and time. If you pay attention, triggers are actually very straightforward, and can clearly be very powerful in conjunction with these date functions. Well, that’s all for now, but hope this knowledge can help you out sometime.

Published
Categorized as Database

By Qushawn Clark

Qushawn is a staff writer for the iEntry Network

Leave a comment