Decomposing Dates or Times Using String Functions

5.7.1 Problem

You want to obtain just a part of a date or a time.

5.7.2 Solution

Treat a temporal value as a string and use a function such as LEFT( ) or MID( ) to extract substrings corresponding to the desired part of the value.

5.7.3 Discussion

Recipe 5.5 and Recipe 5.6 discuss how to extract components of temporal values using DATE_FORMAT( ) or functions such as YEAR( ) and MONTH( ). If you pass a date or time value to a string function, MySQL treats it as a string, which means you can extract substrings. Thus, yet another way to extract pieces of temporal values is to use string functions such as LEFT( ) or MID( ).

mysql> SELECT dt,
 -> LEFT(dt,4) AS year,
 -> MID(dt,9,2) AS day,
 -> RIGHT(dt,2) AS second
 -> FROM datetime_val;
+---------------------+------+------+--------+
| dt | year | day | second |
+---------------------+------+------+--------+
| 1970-01-01 00:00:00 | 1970 | 01 | 00 |
| 1987-03-05 12:30:15 | 1987 | 05 | 15 |
| 1999-12-31 09:00:00 | 1999 | 31 | 00 |
| 2000-06-04 15:45:30 | 2000 | 04 | 30 |
+---------------------+------+------+--------+

You can pull out the entire date or time part from DATETIME values using string-extraction functions such as LEFT( ) or RIGHT( ):

mysql> SELECT dt,
 -> LEFT(dt,10) AS date,
 -> RIGHT(dt,8) AS time
 -> FROM datetime_val;
+---------------------+------------+----------+
| dt | date | time |
+---------------------+------------+----------+
| 1970-01-01 00:00:00 | 1970-01-01 | 00:00:00 |
| 1987-03-05 12:30:15 | 1987-03-05 | 12:30:15 |
| 1999-12-31 09:00:00 | 1999-12-31 | 09:00:00 |
| 2000-06-04 15:45:30 | 2000-06-04 | 15:45:30 |
+---------------------+------------+----------+

The same technique also works for TIMESTAMP values. However, because these contain no delimiter characters, the indexes for LEFT( ) and RIGHT( ) are a little different, as are the formats of the output values:

mysql> SELECT ts,
 -> LEFT(ts,8) AS date,
 -> RIGHT(ts,6) AS time
 -> FROM timestamp_val;
+----------------+----------+--------+
| ts | date | time |
+----------------+----------+--------+
| 19700101000000 | 19700101 | 000000 |
| 19870305123015 | 19870305 | 123015 |
| 19991231090000 | 19991231 | 090000 |
| 20000604154530 | 20000604 | 154530 |
+----------------+----------+--------+

Decomposition of temporal values with string functions is subject to a couple of constraints that component extraction and reformatting functions are not bound by:

  • To use a substring function such as LEFT( ), MID( ), or RIGHT( ), you must have fixed-length strings. MySQL might interpret the value 1987-1-1 as 1987-01-01 if you insert it into a DATE column, but using RIGHT('1987-1-1',2) to extract the day part will not work. If the values have variable-length substrings, you may be able to use SUBSTRING_INDEX( ) instead. Alternatively, if your values are close to ISO format, you can standardize them using the techniques described in Recipe 5.19.
  • String functions cannot be used to obtain values that don't correspond to substrings of a date value, such as the day of the week or the day of the year.

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

Similar book on Amazon

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