How to update a timestamp field of a MySQL table?

Let us first create a table −

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> create table </span><span class="typ">DemoTable</span>
<span class="pun">   -></span><span class="pln"> </span><span class="pun">(</span>
<span class="pun">   -></span><span class="pln"> </span><span class="typ">PunchOut</span><span class="pln"> timestamp</span><span class="pun">,</span>
<span class="pun">   -></span><span class="pln"> </span><span class="typ">PunchStatus</span><span class="pln"> tinyint</span><span class="pun">(</span><span class="lit">1</span><span class="pun">)</span>
<span class="pun">   -></span><span class="pln"> </span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">0</span><span class="pln"> rows affected </span><span class="pun">(</span><span class="lit">0.51</span><span class="pln"> sec</span><span class="pun">)</span>

Insert some records in the table using insert command −

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">DemoTable</span><span class="pln"> values</span><span class="pun">(</span><span class="str">'2019-01-31 6:30:10'</span><span class="pun">,</span><span class="lit">1</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.22</span><span class="pln"> sec</span><span class="pun">)</span>

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">DemoTable</span><span class="pln"> values</span><span class="pun">(</span><span class="str">'2019-02-06 4:10:13'</span><span class="pun">,</span><span class="lit">0</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.14</span><span class="pln"> sec</span><span class="pun">)</span>

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">DemoTable</span><span class="pln"> values</span><span class="pun">(</span><span class="str">'2018-12-16 03:00:30'</span><span class="pun">,</span><span class="lit">0</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.16</span><span class="pln"> sec</span><span class="pun">)</span>

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">DemoTable</span><span class="pln"> values</span><span class="pun">(</span><span class="str">'2016-11-25 02:10:00'</span><span class="pun">,</span><span class="lit">1</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.22</span><span class="pln"> sec</span><span class="pun">)</span>

Display all records from the table using select statemen −

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="kwd">from</span><span class="pln"> </span><span class="typ">DemoTable</span><span class="pun">;</span>

Output

+---------------------+-------------+
| PunchOut            | PunchStatus |
+---------------------+-------------+
| 2019-01-31 06:30:10 |           1 |
| 2019-02-06 04:10:13 |           0 |
| 2018-12-16 03:00:30 |           0 |
| 2016-11-25 02:10:00 |           1 |
+---------------------+-------------+
4 rows in set (0.00 sec)

Here is the query to update the timestamp field of a MySQL table. We have set current date to the fields with PunchStatus 0 −

Note − Current date and time is 2019-06-30 13:43:45

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> update </span><span class="typ">DemoTable</span><span class="pln"> </span><span class="kwd">set</span><span class="pln"> </span><span class="typ">PunchOut</span><span class="pun">=</span><span class="pln">now</span><span class="pun">()</span><span class="pln"> </span><span class="kwd">where</span><span class="pln"> </span><span class="typ">PunchStatus</span><span class="pun">=</span><span class="lit">0</span><span class="pun">;</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pln"> rows affected </span><span class="pun">(</span><span class="lit">0.19</span><span class="pln"> sec</span><span class="pun">)</span>
<span class="typ">Rows</span><span class="pln"> matched</span><span class="pun">:</span><span class="pln"> </span><span class="lit">2</span><span class="pln">  </span><span class="typ">Changed</span><span class="pun">:</span><span class="pln"> </span><span class="lit">2</span><span class="pln"> </span><span class="typ">Warnings</span><span class="pun">:</span><span class="pln"> </span><span class="lit">0</span>

Let us check the table records once again −

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="kwd">from</span><span class="pln"> </span><span class="typ">DemoTable</span><span class="pun">;</span>

Output

+---------------------+-------------+
| PunchOut            | PunchStatus |
+---------------------+-------------+
| 2019-01-31 06:30:10 |           1 |
| 2019-06-30 13:43:45 |           0 |
| 2019-06-30 13:43:45 |           0 |
| 2016-11-25 02:10:00 |           1 |
+---------------------+-------------+
4 rows in set (0.00 sec)