Welcome to WebmasterWorld Guest from 34.231.21.123

Forum Moderators: open

When you need two timestamp fields in a table

     
12:59 am on Apr 19, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1205
votes: 120


I did a quick test, and it looks like TIMESTAMP and BIGINT (14) take up basically the same amount of space. Is that right?

Assuming so, I'm trying to change my USERS table to include a SIGNUPDATE and LASTLOGIN column. Both of these would be TIMESTAMP. I want to set the default to CURRENT_TIMESTAMP, and LASTLOGIN would be "on update CURRENT_TIMESTAMP".

But I get an error that "there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause".

So how would you guys do this? Just set the default for SIGNUPDATE to "0000-00-00 00:00:00"?

In retrospect, I'm not even sure if I need the "on update" clause at all; if I'm updating it, anyway, then I can just send "CURRENT_TIMESTAMP" with the query. So maybe I should just make the default for both "0000-00-00 00:00:00"?
2:58 am on Apr 19, 2019 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member lucy24 is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month

joined:Apr 9, 2011
posts:15934
votes: 889


Can you use a “two steps forward, one step back” approach? Instead of feeding the TIMESTAMP business directly into the table, generate some named variable which happens to use TIMESTAMP, and then populate one column of the database with the value of that variable instead of with TIMESTAMP itself ... and hope the database doesn’t notice.

(This is vague because I don't speak sql; I'm just thinking of it conceptually.)
3:31 am on Apr 19, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1205
votes: 120


I think that you and I are on the same wavelength...

In PHP, I can send a query like:

INSERT INTO table (signupdate, lastlogin) VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)


and then there's no need to have a default setting in the database. It will be a little more work on my end to modify all of the existing queries to include something for lastlogin, but I'm leaning towards that being the best solution.
11:34 am on Apr 19, 2019 (gmt 0)

Junior Member

joined:Apr 21, 2016
posts: 69
votes: 14


Just did a search and found this on the wayback machine. Hopefully this will help with what you are trying to do.
-------------------------------

MySQL using two timestamps for created/updated values
I’ve seen this on and off for years, but never have it handy. So, here’s a quick demo:

mysql> create table sample ( timeUpdated TIMESTAMP, timeCreated TIMESTAMP, val INT );
Query OK, 0 rows affected (0.24 sec)

mysql> insert into sample (timeUpdated, timeCreated, val) values (NULL,NULL,1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from sample;
+———————+———————+——+
| timeUpdated | timeCreated | val |
+———————+———————+——+
| 2007-10-24 15:25:03 | 2007-10-24 15:25:03 | 1 |
+———————+———————+——+
1 row in set (0.00 sec)

mysql> update sample set val=val+1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from sample;
+———————+———————+——+
| timeUpdated | timeCreated | val |
+———————+———————+——+
| 2007-10-24 15:25:23 | 2007-10-24 15:25:03 | 2 |
+———————+———————+——+
1 row in set (0.00 sec)

The basic idea is that by forcing NULLs in to the TIMESTAMP columns, they’ll both be triggered to use the current timestamp value. But when you don’t reference them, only the first TIMESTAMP column will be updated, and if you define it as the ‘last updated’ column, you’re good to go.