0

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?

  • 1
    utc_date is a reserved function. My guess is that it's interpreting it literally and giving you the current utc_date. Try addressing the column surrounded in accent marks. SELECT * FROM shift WHERE utc_date LIKE '%2016%' – Jonathan Fite Apr 21 '16 at 21:02
  • The thing is that it USED to work fine, until I ran that one query with curdate(). – MaQleod Apr 21 '16 at 21:20
  • 1
    You'll always have to use backticks enclosing utc_date to make it work. But it is a recipe for trouble, you may want to consider renaming the column. – Ziggy Crueltyfree Zeitgeister Apr 22 '16 at 0:38
  • 1
    @ZiggyCrueltyfreeZeitgeister - write that as an Answer. – Rick James Apr 24 '16 at 21:37
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.