FreeBSD - Tutorials, Security
Home   Archives   Sitemap   About   Contact

How to calculate difference in days between two dates in MySQL

Home NEW! Unix Forum News 100 Tips and Tricks Website Development Server Operating Systems Databases
 Ivorde.ROarrow Databases arrowGeneral DevelopmentarrowHow to calculate difference in days between two dates in MySQL 

Article Sections

    Hello, Guest !
User name:
Password:
 
Google

 SSD VPS Hosting - Vpsie.com
 Mo.nitor.me
 Ivorde forum
 FreeBSD Tutorials
 Linux LVM Commands
 Free Shell Accounts
 FreeBSD Project
 FreeBSD Handbook
 Advanced Bash-Scripting Guide
 The OpenBSD Project
 Distrowatch
 FreeBSD Handbook


Apache Webserver Home Page

Posted on: 20 Feb 2008
Author: mandrei
Section: Databases | General Development
Views: 2305
Comments: 7 (Add)

How to calculate difference in days between two dates in MySQL
mysql difference between two dates, calculate day difference

 



How to calculate difference in days between two dates in MySQL

Let's say we have a MySQL table where one column (date or datetime type) is named activated and contains dates in one of the YYYY-MM-DD or YYYY-MM-DD HH:MM:SS formats from the past and we need to calculate the difference in days since that date untill current date for each row.

First let's see a preview of the activated column:

mysql> SELECT activated FROM table_dates LIMIT 0,5;
+--------------+
| activated     |
+--------------+
| 2007-06-06 |
| 2007-10-15 |
| 2007-10-17 |
| 2007-10-18 |
| 2007-10-19 |
+--------------+
5 rows in set (0.00 sec)
mysql>

so we see dates lik 06th of June, 15th of October and so on. Now, let's calculate the difference between these dates and current date:


mysql> SELECT DATEDIFF(CURDATE(), activated) AS intval FROM table_dates LIMIT 0,5;
+--------+
| intval   |
+--------+
|    259  |
|    128  |
|    126  |
|    125  |
|    124  |
+--------+
5 rows in set (0.00 sec)

so the difference in days between 6th of June and present day is 259 days. Same with the others.

I used in the queries above two MySQL date functions: DATEDIFF() and CURDATE().

Also take a good look at MySQL Date and Time Functions and MySQL The DATETIME, DATE, and TIMESTAMP Column Types.

Bookmarks: Echo "How to calculate difference in days between two dates in MySQL" around:
del.icio.usdiggFurlYahooMyWebGoogleBookmarksFaceBookTechnocratti
-------------------advertising-----------------

Other articles in Databases / General Development
» MySQL table - change or rename MySql table name
» How to set up a password for root user in MySql
» Recover a lost MySQL root password
» How to copy a mysql database using mysqldump
» How to backup all mysql databases with one command




Contact webmaster regarding this article
Register or Login to post your article
Hello, Guest ! You can Login or Register to www.ivorde.ro!

 Post comment:

Name:
Title:
Comment:
Please type the word you see in the image (anti-spam verification). Refresh the page if you don't understand the word.
Allowed HTML Tags for comments:<p><strong><em><u><h1><h2><h3><h4><h5><h6><img><li>
<ol><ul><span><div><br><ins><del>

7 comment(s) to How to calculate difference in days between two dates in MySQL:

1. Re: How to calculate difference in days between two dates in MySQL
Display the difference in HTML table by Samuel at July 01st, 2013 - 08:50
Hi, is it possible to display the difference in HTML table if so please kindly help

2. Re: How to calculate difference in days between two dates in MySQL
fgh by fgh at November 29th, 2011 - 12:39
dfghdfgh

3. Re: How to calculate difference in days between two dates in MySQL
sujit by angerj at September 20th, 2011 - 03:15
gfg

4. Re: How to calculate difference in days between two dates in MySQL
Re: How to calculate difference in days between tw by suresh at July 15th, 2011 - 03:10
Works great, thanks

5. Re: How to calculate difference in days between two dates in MySQL
developer by mahmud at December 23rd, 2010 - 23:30
usefull

6. Re: How to calculate difference in days between two dates in MySQL
Great by Efren at December 15th, 2010 - 18:35
Works great, thanks

7. Re: How to calculate difference in days between two dates in MySQL
Great tip! by Henrik at November 01st, 2010 - 09:23
Great tip.
Thanks!

   Latest topics on the forum:
 
   Most viewed articles:
How to copy a mysql database using mysqldump - 9375 views
How to change a user's password in AIX with the output from ECHO command - 8635 views
FreeBSD: Add/remove an additional IP alias - 5609 views
Qmail relay to smarthost: How to route all mail to a smarthost - 3929 views
Print queues in AIX 5L - How to list print queues in AIX - 2884 views

   Latest 10 articles:
Qmail relay to smarthost: How to route all mail to a smarthost - 03 Feb 2009
EXIM 4 relay to smarthost: How to route all mail except local domain - 03 Feb 2009
Windows XP: print LISTEN ports and network connections using netstat - 30 Jan 2009
How to cut out first last n characters from each file name, from a filelist - 04 Nov 2008
Mozilla Firefox3 is now released - 18 Jun 2008
How to switch lower case to upper case and upper case to lower case in a string - 17 Jun 2008
How to rename files/directories to uppercase/lowercase character names - 17 Jun 2008
How to convert lower case to upper case letters in a shell script/command - 17 Jun 2008
Unix,Linux,FreeBSD - How to rename a list of files, replacing spaces inside their names - 12 Jun 2008
How to change a user's password in AIX with the output from ECHO command - 21 May 2008


Archives
» 2007  |  June  |  October  |  November  |  December
» 2008  |  January  |  February  |  March  |  April  |  May  |  June  |  November
» 2009  |  January  |  February



Home | Archives | Sitemap | About | Contact

Designed and developed by Andrei Manescu. Optimized for Mozilla Firefox.  
Copyright 2007 Andrei Manescu
All trademarks and copyrights on this page are owned by their respective owners. Comments are owned by those who posted them.
Valid W3 Document Valid XHTML 1.0 Transitional Valid CSS! The FreeBSD Project Viewable With Any Browser