If have been trying to keep track of multiple timestamps regarding user-related inputs and my idea was that each column is a user_id written as 1234 and when a user does something it adds a timestamp
| used_id | used_id | used_id |
|---|---|---|
| TIMESTAMP | TIMESTAMP | TIMESTAMP |
| TIMESTAMP | TIMESTAMP | TIMESTAMP |
| TIMESTAMP | TIMESTAMP | TIMESTAMP |
the thing is that when I insert NOW() values for each user_id it shifts it like this
| 1234 | 4321 | 2341 |
|---|---|---|
| 2021-04-19 02:00:00 | NULL | NULL |
| NULL | 2021-04-19 03:00:00 | NULL |
| NULL | NULL | 2021-04-19 04:00:00 |
| 2021-04-20 02:00:00 | NULL | NULL |
| NULL | 2021-04-20 03:00:00 | NULL |
| NULL | NULL | 2021-04-20 04:00:00 |
even tho I want it like this
| 1234 | 4321 | 2341 |
|---|---|---|
| 2021-04-19 02:00:00 | 2021-04-19 03:00:00 | 2021-04-19 04:00:00 |
| 2021-04-20 02:00:00 | 2021-04-20 03:00:00 | 2021-04-20 04:00:00 |
Is there another way to achieve something like this? Thank you
Advertisement
Answer
Here’s a normalised arrangement:
user_id timestamp 1234 2021-04-19 02:00:00 4321 2021-04-19 03:00:00 2341 2021-04-19 04:00:00 1234 2021-04-20 02:00:00 4321 2021-04-20 03:00:00 2341 2021-04-20 04:00:00
or
reading_id user_id timestamp
1 1234 2021-04-19 02:00:00
1 4321 2021-04-19 03:00:00
1 2341 2021-04-19 04:00:00
2 1234 2021-04-20 02:00:00
2 4321 2021-04-20 03:00:00
2 2341 2021-04-20 04:00:00