Date and Time Manipulation

# Find dates newer than one week ago SELECT users FROM accounts   WHERE created >=         DATE_SUB(CURDATE(), INTERVAL 1 WEEK); 

Basic manipulations and comparisons of dates and times within MySQL are quite simple.

MySQL allows date and time information to be conveniently represented in the international format of 'YYYY-MM-DD HH:MM:SS' (for example, '2005-09-30 18:43:01') or a more compact representation of YYYYMMDDHHMMSS (for example, 20050930184301).

A variety of useful functions, such as DATE_ADD() or DATE_SUB(), can be used to add and subtract dates and times, extract fragments such as hours or year from a date/time, and so on.

Additionally, the standard mathematical comparison operators of greater than (>), less than (<), equivalence (=), and so on, can be used to compare dates to each other.

Some of the most commonly used parts of MySQL's date and time functionality are the NOW(), CURDATE(), and CURTIME() functions. CURDATE() returns the current date, while, not surprisingly, CURTIME() returns the current time. NOW() returns both the current date and time.

In the phrasebox sample, the greater-than-or-equals operator (>=) is used in conjunction with CURDATE() and DATE_SUB() to find all users in a fictional accounts table who were created between now and the past week. In this case, you assume that the created column contains dates.

Most manipulations of date and time are simple enough and behave as expected. However, beware of time zones, daylight savings time, calendar changes for historical dates, wandering system clocks, two-digit century representations, overly short timestamps, and other fearsome beasts as you design any program that deals with dates and times on a larger scale.

A few simple tips to help keep you out of trouble are

  • Use Network Time Protocol (NTP) to keep your computer's time up-to-date.

  • When possible, use more precision than you need to store your dates.

  • If you need to be precise about times, store them in Coordinated Universal Time (UTC), also known as Greenwich Mean Time (GMT), and then add logic in your programs to display the appropriate local time for users.

For more information on working with dates and times, see the MySQL online manual, these sections in particular:



MySQL Phrasebook. Essential Code and Commands
MySQL Phrasebook
ISBN: 0672328399
EAN: 2147483647
Year: 2003
Pages: 130

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: