When you need two timestamp fields in a table

Forum Moderators: open

Message Too Old, No Replies

When you need two timestamp fields in a table

csdude55

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

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"?

lucy24

2:58 am on Apr 19, 2019 (gmt 0)

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.)

csdude55

3:31 am on Apr 19, 2019 (gmt 0)

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.

ipco

11:34 am on Apr 19, 2019 (gmt 0)

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.