In phpmyadmin is this the best time stamp

Databases
1

I want to add a time/date column in a db table. Is this sufficient (or wrong)? Any suggestions are welcome

CREATE TABLE timestamp_n_datetime (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ts TIMESTAMP,
    dt DATETIME
);
2

Iā€™m uncertain why youā€™d store both a Timestamp and a Datetime. Whatā€™s your use case?

3

I would just like a Datetime

4

here ya go ā€“

CREATE TABLE datetime_only_no_timestamp 
( id INTEGER  NOT NULL AUTO_INCREMENT PRIMARY KEY
, dt DATETIME
);
5

Iā€™m assuming youā€™re aware of the differences? eg. supported ranges, UTC conversion, etc.

1 Like
6

no, not aware

7

You should give this at least a quick read. Thereā€™s quite a bit to it but I think if thereā€™s anything you may need to consider you should recognize it as such when you see it.

https://dev.mysql.com/doc/refman/8.0/en/datetime.html

The DATE , DATETIME , and TIMESTAMP types are related. This section describes their characteristics, how they are similar, and how they differ.

2 Likes
8

Thank you. Regarding your "here ya go CREATE TABLE etc

wouldnā€™t it be ALTER TABLE? since the date time will be a new column in an existing db table?

1 Like
9

Hello,

DATETIME and TIMESTAMP datatype is same, however, the range differs for them. i.e. DATETIME supports ā€˜1000-01-01 00:00:00.000000ā€™ to ā€˜9999-12-31 23:59:59.999999ā€™ whereas TIMESTAMP supports ā€˜1970-01-01 00:00:01.000000ā€™ to ā€˜2038-01-19 03:14:07.999999ā€™. in most of the cases, we need either one of them. we may need both of them specifically in very exceptional cases.

10

Thank you.
Wouldnā€™t it be ALTER TABLE(instead of CREATE TABLE)? since the date time will be a new column in an existing db table?

11

It depends. Your OP has a CREATE TABLE example with both timestamp and datetime fields. You then posted that you would ā€œjust like a datetimeā€. It could be inferred that because the example was a CREATE TABLE, that you wanted a CREATE TABLE answer.

If you are wanting to add a datetime field to an existing table then yes, it should be an ALTER TABLE instead.

13

Thank you. I tried this and itā€™s reporting that this is not a valid query:

ALTER TABLE datetime_only_no_timestamp ( 
    id INTEGER  NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    dt DATETIME
);

any additional help will be welcomed

14

Well, two means to go about it:
1: DROP TABLE IF EXISTS datetime_only_no_timestamp; CREATE TABLEā€¦
2: ALTER TABLE datetime_only_no_timestamp DROP COLUMN ts;

You canā€™t alter a table that doesnt exist,
and the ALTER syntax requires either a series of ADD, DROP, or ALTER/MODIFY (depending on engine) subclauses to identify WHAT you want to alter about the table. It doesnt accept a schema, because it would be ambiguous how you wanted to modify the table.

EDIT: Make the drop flexible to the existence state of the table.

15

Thanks for your reply, Iā€™m trying to add a time stamp to an existing db table, what would you suggest?

16

Well if itā€™s an existing table with data in it, destroying the table would generally be considered a bad idea. So iā€™d alter it. Instead of DROPPING a column, ADD a column. (See link in post 14)

17

Thanks again.
Would this be valid:

ALTER TABLE users
ADD time_date ( 
    id INTEGER  NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    dt DATETIME
);
18

ā€¦no.
Why are you trying to add an integer and a datetime?

ALTER TABLE users
ADD time_date DATETIME;
19

thanks again

20

what happened when you tested it? ā„¢

1 Like
21

@ChrisjChrisj,
Is it possible to copy and download the table?

I would prefer to use PhpMyAdmin locally to make changes because invalid SQL statements have no effect on the table.

Also try the ā€œformatā€ option because invalid script is highlighted.