Converting Between Dates and Days

5.12.1 Problem

You have a date but want a value in days, or vice versa.

5.12.2 Solution

DATE values can be converted to and from days with TO_DAYS( ) and FROM_DAYS( ). Date-and-time values also can be converted to days if you're willing to suffer loss of the time part.

5.12.3 Discussion

TO_DAYS( ) converts a date to the corresponding number of days, and FROM_DAYS( ) does the opposite:

mysql> SELECT d,
 -> TO_DAYS(d) AS 'DATE to days',
 -> FROM_DAYS(TO_DAYS(d)) AS 'DATE to days to DATE'
 -> FROM date_val;
+------------+--------------+----------------------+
| d | DATE to days | DATE to days to DATE |
+------------+--------------+----------------------+
| 1864-02-28 | 680870 | 1864-02-28 |
| 1900-01-15 | 693975 | 1900-01-15 |
| 1987-03-05 | 725800 | 1987-03-05 |
| 1999-12-31 | 730484 | 1999-12-31 |
| 2000-06-04 | 730640 | 2000-06-04 |
+------------+--------------+----------------------+

When using TO_DAYS( ), it's probably best to stick to the advice of the MySQL Reference Manual and avoid DATE values that occur before the beginning of the Gregorian calendar (1582). Changes in the lengths of calendar years and months prior to that date make it difficult to speak meaningfully of what the value of "day 0" might be. This differs from TIME_TO_SEC( ), where the correspondence between a TIME value and the resulting seconds value is obvious and has a meaningful reference point of 0 seconds.

If you pass TO_DAYS( ) a date-and-time value, it extracts the date part and discards the time. This provides another means of extracting dates from DATETIME and TIMESTAMP values:

mysql> SELECT dt,
 -> TO_DAYS(dt) AS 'date part in days',
 -> FROM_DAYS(TO_DAYS(dt)) AS 'date part as DATE'
 -> FROM datetime_val;
+---------------------+-------------------+-------------------+
| dt | date part in days | date part as DATE |
+---------------------+-------------------+-------------------+
| 1970-01-01 00:00:00 | 719528 | 1970-01-01 |
| 1987-03-05 12:30:15 | 725800 | 1987-03-05 |
| 1999-12-31 09:00:00 | 730484 | 1999-12-31 |
| 2000-06-04 15:45:30 | 730640 | 2000-06-04 |
+---------------------+-------------------+-------------------+
mysql> SELECT ts,
 -> TO_DAYS(ts) AS 'date part in days',
 -> FROM_DAYS(TO_DAYS(ts)) AS 'date part as DATE'
 -> FROM timestamp_val;
+----------------+-------------------+-------------------+
| ts | date part in days | date part as DATE |
+----------------+-------------------+-------------------+
| 19700101000000 | 719528 | 1970-01-01 |
| 19870305123015 | 725800 | 1987-03-05 |
| 19991231090000 | 730484 | 1999-12-31 |
| 20000604154530 | 730640 | 2000-06-04 |
+----------------+-------------------+-------------------+

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net