PHPmyadmin current_timestamp() default not working

Hosting Support
1

Website URL

tdsd.great-site.net

Error Message

1 row inserted.
Inserted row id: 1
Warning: #1265 Data truncated for column ‘date’ at row 1

Other Information

Hi,

I have an issue with my PHPmyadmin. When setting a column default to current_timestamp() and creating an entry via the “insert” screen, I get the error message displayed about data truncation, and I get 0000-00-00 for the date in my new DB entry. The reason is that current_timestamp() is being passed in as a string. If I manually delete the quotation marks, the current date populates the column as intended. Here is the generated SQL:

INSERT INTO datetime_test (id, date) VALUES (NULL, ‘current_timestamp()’);

If I manually select current_date or another function when creating the entry, the current date is passed in properly, but that defeats the purpose of setting a default (I want the entries dated for when I add them to the DB)

2

It’s recommended to always work with timestamps on PHP instead of on MySQL directly.

Even if you managed to get this working, there’s a high chance that the time will be different than what you expect because timestamps generated from MySQL will always use the same timezone as the server itself, while PHP allows you to customize the timezone.

If you really want this to work I suggest you to run the SQL by yourself instead of trying to use phpMyAdmin.

ALTER TABLE datetime_test 
MODIFY COLUMN date TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
3

The “issue” you’re describing is just how MySQL works. If you pass in a string, you get a string. If you want to use a MySQL function, you need to correctly reference it as a function and not as a string.

If you try to pass a string to a date field, you won’t get a correct value.

You already correctly identified the issue and the solution: don’t pass it as a string, which means don’t add quotes around the function.

I’m not sure how that’s related to the first part of your message. Regardless of whether you are using an explicit string value or a function in your query: you’re passing an explicit value, and not using the database default.

If this whole thing started by trying to bake the current_timestamp() function into the table definition, e.g. you were trying to do something like this:

ALTER TABLE datetime_test 
MODIFY COLUMN date TIMESTAMP DEFAULT current_timestamp();

Then that indeed won’t work, because MySQL doesn’t support functions for defaults. Like @Meishin pointed out, you can use the CURRENT_TIMESTAMP thing instead.

4

Sorry for the confusion or my poor wording, but thanks for fast follow up @Meishin and @Admin

I am setting the CURRENT_TIMESTAMP as default via the phpmyadmin GUI drop-down menu in the Create Table tab (or via the “change column” form). I have also tried setting it as default via SQL code directly, on the SQL tab (still in the phpmyadmin interface).

But when I insert a new item, the default value renders with quotes and parentheses as I showed in the first post. When I go back into the “change column” GUI form, the drop-down CURRENT_TIMESTAMP selection is not shown as default, but instead As defined: is selected. In the text box, current_timestamp() is written.

To clarify, I initially choose the CURRENT_TIMESTAMP dropdown option, but this is changed to As defined: current_timestamp() after submitting the SQL statement.

If I try a demo database at phpmyadmin.net I have no similar issue.

I would like to try again and take screenshots or re-verify that I did not mess something up, following the recommendations you’ve provided. But, unfortunately, after posting this (publishing the URL on this forum), I hit 25K hits around 10AM yesterday and 50k hits around 3PM, after having approximately 20 hits/day previously (I think my website was viewed primarily by myself and 1 friend, with a few other friends viewing on occasion). So I need to resolve something there as soon as my suspension resolves. Is there any easy way to temporarily take my website offline or unviewable once my account is unsuspended so I can try to find any weird page hit multiplication glitches I may have? I don’t know if I got hit by crawlers or DoS attack or what.

In response to a couple of your points,@Meishin : If I understand correctly, the DB is in EST, and I am too. I am not worried if things are off by 1 hour during DST. I wouldn’t even be worried if the DB was in any other timezone. I am basically concerned about relative timestamps for personal reference.

As for running the SQL without using phpmyadmin, am I correct that I would need to have a webpage on my site with php code connecting to the DB and running the SQL command shown? From what I understand, the only way to access the databases here on IF is to use the phpmyadmin panel or via php in the webpage itself. If I am incorrect in that understanding, let me know.

Best, Patrick Wright

5

Site is back up and running, and the PHP myadmin Default_Timestamp that was giving me issues is working flawlessly. I don’t know what I was doing wrong. Maybe I had date selected, selected current_timestamp, and did not reset the column default properly after changing the column to datetime, although I certainly tried.

Perhaps I should put some sort of analytics on my page, in case I have another 606,000 hit day.