Database Reference
In-Depth Information
shown in the example in that same section, enter a time value for
id_start
us-
ing
CURTIME()
, but enter NULL for
id_end
. Then run an
UPDATE
statement
after each
INSERT
to set the time for
id_end
, using
CURTIME()
again so that
the times will be different. Wait a short amount of time between the
INSERT
and
the
UPDATE
for each row.
After you've entered several more rows to
bird_identification_tests
,
construct a
SELECT
statement using the
TIMEDIFF()
function to compare the
difference in the times of
id_start
and
id_end
for each row. Be sure to put
the columns in the correct order within
TIMEDIFF()
so that the results do not
show negative values. Include the first name of each person in the SQL statement.
4. Put together another
SELECT
statement to get
common_name
from the
birds
table, and the
id_start
and
id_end
columns from the
birdwatchers
table. Use the
TIMEDIFF()
function to compare the differences in time between
the two columns containing times. When you join the two tables, remember to ad-
just the
JOIN
to reflect that they are in separate databases. When that's finished,
execute the
SELECT
statement to be sure it's constructed properly. Then add a
GROUP BY
clause to group by
bird_id
, and wrap
TIMEDIFF()
in
AVG()
to
get the average time. Give that field an alias of
Avg. Time
or something simil-
ar. Run that statement to see the results. The results for the average time field
should include a number with four decimal places, all zeros (e.g.,
219.0000
for
2 minutes, 19 seconds).
Next, redo the
SELECT
statement to convert the average time from a number
with four decimal places to the
TIME
format. To do this, first use the
TRIM()
function with the
TRAILING
option and give it a string of
.0000
to trim that
string from the end of the average time. Run the
SELECT
to see the results of that
addition. Then, wrap all of that in
LPAD()
to make sure there's enough zeros to
conform to this format:
hhmmss
. Run the
SELECT
statement again to see the im-
provements in the results. Both of these string functions were covered in
Trim-
Finally, use
STR_TO_DATE()
to convert the padded number (e.g.,
000219
) to
you provide only formatting codes for time elements,
STR_TO_DATE()
will re-
turn only time information, which is what we want for this exercise. Execute the
SELECT
when you're finished to make sure it's correct. Make corrections until
you get it to work.
