Mysql – column values do not return properly when column name called out specifically in select statement

datetimeMySQL

I have a database with this table (version 5.0.95):

> describe shift;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int(11)      | NO   | PRI | NULL    | auto_increment |
| person_id       | int(11)      | NO   | MUL | NULL    |                |
| utc_date        | datetime     | NO   | MUL | NULL    |                |
| mins            | int(11)      | NO   |     | NULL    |                |
| active_utc_date | datetime     | YES  |     | NULL    |                |
| active_mins     | int(11)      | YES  |     | NULL    |                |
| absence_id      | int(11)      | YES  |     | NULL    |                |
| updated_by      | int(11)      | YES  |     | NULL    |                |
| updated_name    | varchar(100) | YES  |     | NULL    |                |
| updated_date    | datetime     | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+

Sample of what it looks like is here:

| 9502849 |    327037 | 2017-06-12 11:00:00 |  540 | 2017-06-12 11:00:00 |         510 |         -1 |       NULL | NULL          | 2015-01-01 00:00:00 |
| 9502850 |    327037 | 2017-06-13 11:00:00 |  540 | 2017-06-13 11:00:00 |         510 |         -1 |       NULL | NULL          | 2015-01-01 00:00:00 |
| 9502851 |    327037 | 2017-06-14 11:00:00 |  540 | 2017-06-14 11:00:00 |         510 |         -1 |       NULL | NULL          | 2015-01-01 00:00:00 |
| 9502852 |    327037 | 2017-06-15 11:00:00 |  540 | 2017-06-15 11:00:00 |         510 |         -1 |       NULL | NULL          | 2015-01-01 00:00:00 |
| 9502853 |    327037 | 2017-06-16 11:00:00 |  540 | 2017-06-16 11:00:00 |         510 |         -1 |       NULL | NULL          | 2015-01-01 00:00:00 |
+---------+-----------+---------------------+------+---------------------+-------------+------------+------------+---------------+---------------------+
940 rows in set (0.09 sec)

I wanted to see entries with utc_date as just today:

select utc_date from shift where (person_id = 327037 and date(utc_date) = curdate());

| 2016-04-21 |
| 2016-04-21 |
| 2016-04-21 |
| 2016-04-21 |
| 2016-04-21 |
| 2016-04-21 |
| 2016-04-21 |
| 2016-04-21 |
+------------+
940 rows in set (0.08 sec)

That wasn't great, because uct_date got changed to be curdate()…
And now, anytime I try to look at utc_date, it always shows that same date:

select utc_date from shift where (person_id = 327037 and utc_date like '%2016%');

| 2016-04-21 |
| 2016-04-21 |
| 2016-04-21 |
| 2016-04-21 |
| 2016-04-21 |
| 2016-04-21 |
| 2016-04-21 |
+------------+
940 rows in set, 1 warning (0.08 sec)

But if I view the table with a *, I still see it as it should be:

select * from shift where person_id = 327037;

| 9502849 |    327037 | 2017-06-12 11:00:00 |  540 | 2017-06-12 11:00:00 |         510 |         -1 |       NULL | NULL          | 2015-01-01 00:00:00 |
| 9502850 |    327037 | 2017-06-13 11:00:00 |  540 | 2017-06-13 11:00:00 |         510 |         -1 |       NULL | NULL          | 2015-01-01 00:00:00 |
| 9502851 |    327037 | 2017-06-14 11:00:00 |  540 | 2017-06-14 11:00:00 |         510 |         -1 |       NULL | NULL          | 2015-01-01 00:00:00 |
| 9502852 |    327037 | 2017-06-15 11:00:00 |  540 | 2017-06-15 11:00:00 |         510 |         -1 |       NULL | NULL          | 2015-01-01 00:00:00 |
| 9502853 |    327037 | 2017-06-16 11:00:00 |  540 | 2017-06-16 11:00:00 |         510 |         -1 |       NULL | NULL          | 2015-01-01 00:00:00 |
+---------+-----------+---------------------+------+---------------------+-------------+------------+------------+---------------+---------------------+
940 rows in set (0.10 sec)

So I know that it didn't change in the table – but for whatever reason, when I try to display the field by name, it seems to remember whatever caused it to display as today (presumably it took this as an assignment: date(utc_date) = curdate()).

How do I undo that assignment?

Best Answer

As @JonathanFite stated in the comments, date_utc is a system function, so your queries will seem to work when your field happens to have today's date, but will fail for any others (because it's not reading the field, but returning the value of the system function instead). You'll always have to use backticks enclosing utc_date to make it work. Keeping this name, however, it is a recipe for trouble, you may want to consider renaming the column.

Related Question