Inline Lab 3-B
Step 4: Cleaning up the output
3.8 Dates to Strings
Extracting portions of a date field to display a string (or integer) value can be accomplished with numerous SQL functions. The following list demonstrates a portion of these functions:
79
Function Description Usage Result
DAY() Returns the day of the month (1-31) DAY('2008-02-26') 26 DAYOFMONTH() Synonym for DAY() function DAYOFMONTH('2008-02-26') 26 DAYNAME() Returns the name of the weekday DAYNAME('2008-02-26') Tuesday DAYOFWEEK() Returns the weekday index (1-7) DAYOFWEEK('2008-02-26') 3
DAYOFYEAR() Returns the day of the year (1-366) DAYOFYEAR('2008-02-26') 57 MONTH() Returns the month (1-12) MONTH('2008-02-26') 2
MONTHNAME() Returns the name of the month MONTHNAME('2008-02-26') February QUARTER() Returns the quarter of the year QUARTER('2008-02-26') 1
WEEK() Returns the week of the year WEEK('2008-02-26',3) 8 WEEKDAY() Returns the weekday index (0-6) WEEKDAY('2008-02-26') 1 YEAR() Returns the 4-digit year YEAR('2008-02-26') 2008 YEARWEEK() Returns the year and week YEARWEEK('2008-02-26',3) 200808
How is the first week of the year calculated?
When using the WEEK() function, there are eight different modes that can be used to determine how the weeks are calculated. The modes are entered into the second argument of the function and range from 0 (which states that if the first week of the year contains a Sunday, consider if the zero week for the year using a range of 0-53) to 7 (which states that if the first week contains a Monday, consider it the first week of the year using a range of 1-53). The other modes (1-6) provide more options to fine tune the WEEK() function. The WEEKDAY() function does not take a mode argument and is equivalent to WEEK(date,3) which states that if the first week contains more than 3 days and begins with a Monday, consider it the first week using a range of 1-53.
Thi Nguyet Tran (moonฺtran@spts21ฺcomฺvn) has a non-transferable
license to use this Student Guideฺ
3.8.1 Time values to Strings
As seen, MySQL has numerous date functions that will extract specific values from the dates entered. In addition to date functions, MySQL offers multiple time functions that can be valuable to administrators and developers alike.
• TIME() - This function will return the time portion of a date (and/or time) value. This
function should only be used with valid time values to assure accurate responses.
mysql> SELECT TIME('1955-11-12 22:04:00'); +---+
| TIME('1955-11-12 22:04:00') | +---+ | 22:04:00 | +---+ 1 row in set (#.## sec)
• HOUR() - This function will return the hours for a day from 0 to 23 and greater if the value given contains a greater hour value.
mysql> SELECT HOUR('1955-11-12 22:04:00'); +---+
| HOUR('1955-11-12 22:04:00') | +---+ | 22 | +---+ 1 row in set (#.## sec)
• MINUTE() - This function will return the minutes from a given time in the range of 0-59.
mysql> SELECT MINUTE('1955-11-12 22:04:00'); +---+
| MINUTE('1955-11-12 22:04:00') | +---+ | 4 | +---+ 1 row in set (#.## sec)
• SECOND() - This function will return seconds from a given time also in the range of 0-59.
mysql> SELECT SECOND('1955-11-12 22:04:05'); +---+
| SECOND('1955-11-12 22:04:00') | +---+ | 5 | +---+ 1 row in set (#.## sec)
80
3-24 ______________________________________________________________________________________________ ______________________________________________________________________________________________ ______________________________________________________________________________________________
Thi Nguyet Tran (moonฺtran@spts21ฺcomฺvn) has a non-transferable
license to use this Student Guideฺ
• MICROSECOND() - This function will return the microseconds from a given time expression as a number in the range from 0 to 999999.
mysql> SELECT MICROSECOND('1955-11-12 22:04:00.00078405'); +---+
| MICROSECOND('1955-11-12 22:04:00.00078405') | +---+ | 78405 | +---+ 1 row in set (#.## sec)
3.8.1.1 Storing Microsecond Precision Times
Microseconds can not be stored into a column of any of the MySQL temporal data types. If an attempt is made to store a time value with a microsecond into a temporal data type, the microsecond value is discarded.
Storing as a DOUBLE
One of the ways that a time value can be stored is by converting the time to a numeric Unix timestamp. The Unix timestamp evaluates the current time (or the time provided) into an unsigned integer that equates to the number of seconds since '1970-01-01 00:00:00'. This Unix timestamp can be evaluated by issuing the UNIX_TIMESTAMP function:
mysql> SELECT UNIX_TIMESTAMP('2008-02-26 09:00:00'); +---+
| UNIX_TIMESTAMP('2008-02-26 09:00:00') | +---+ | 1204034400 | +---+ 1 row in set (#.## sec)
Once the time is an integer value, the microsecond value can be added to this value and then stored in a data type value such as DOUBLE. For example, the following table could be used to store the requested time that a web page was requested by a browser and the actual time that the server took to evaluate the request and begin a response back to the browser:
mysql> CREATE TABLE web_request ( -> web_page VARCHAR(100), -> request_time DOUBLE(16,6), -> response_time DOUBLE(16,6) -> );
Query OK, 0 rows affected (#.## sec)
With this table in place, statistics can begin to be evaluated for each web page requested to the microsecond (up to 6 values) based on the DOUBLE data type being used (16 precision of significant digits being stored, with up to 6 of those digits being stored after the decimal point).
81
Thi Nguyet Tran (moonฺtran@spts21ฺcomฺvn) has a non-transferable
license to use this Student Guideฺ
Continuing with the example, the web_request table that was created will be filled with a record that will store the requested time that a web page on the mysql.com domain server received the request for access to one of its pages and when the server started the response back to the browser requesting the page:
mysql> INSERT INTO web_request VALUES (
-> 'www.mysql.com/training/catalog.html',
-> UNIX_TIMESTAMP('2008-02-26 09:00:00')+.001356, -> UNIX_TIMESTAMP('2008-02-26 09:00:00')+.978650 -> );
Query OK, 1 row affected (#.## sec)
The following query will show how the record just added was actually stored in the web_request table:
mysql> SELECT web_page, request_time, response_time FROM web_request;
+---+---+---+ | web_page | request_time | response_time | +---+---+---+ | www.mysql.com/training/catalog.html | 1204034400.001356 | 1204034400.978650 | +---+---+---+ 1 row in set (#.## sec)
With these values, along with additional requests over a set period of time, stored in the web_request table, statistics can be evaluated and monitored. The following query uses the one record stored to determine a simple length of time scenario from when a page is requested and when the server begins to respond in milliseconds:
mysql> SELECT response_time - request_time AS diff FROM web_request; +---+
| diff | +---+ | 0.977294 | +---+
1 row in set (#.## sec)
83 82 3-26 ______________________________________________________________________________________________ ______________________________________________________________________________________________ ______________________________________________________________________________________________
Thi Nguyet Tran (moonฺtran@spts21ฺcomฺvn) has a non-transferable
license to use this Student Guideฺ
One of many
The solution to storing microseconds presented is one of many ways to accomplish this task. The following are a list of a few other ways to accomplish this:
● BIGINT - Using the BIGINT data type, the time value would be stored as an integer (which implies no decimals). This would be accomplished by multiplying the Unix timestamp by 100,000 to make room for the microseconds to be stored in the last six values of the integer:
mysql> SELECT UNIX_TIMESTAMP('2008-02-26 09:00:00') * 100000 + 001356; +---+
| UNIX_TIMESTAMP('2008-02-26 09:00:00') * 100000 + 001356 | +---+ | 120403440001356 | +---+ 1 row in set (#.## sec)
● Two columns - Storing the date/time value in one column and the microseconds in a second column can eliminate the need to use Unix timestamps:
mysql> SELECT '2008-02-26 09:00:00' AS sec, 978650 AS micro; +---+---+
| sec | micro | +---+---+ | 2008-02-26 09:00:00 | 978650 | +---+---+ 1 row in set (#.## sec)