Have Geek Brain

How to insert date into mysql table

I have a mysql table called pollOfTheWeek. It has a "pollDate" column of type date. I have two questions regarding this: 1. I declared the column when creating the table as [pollDate]. I wanted this column to be set automatically when the user does not enter values ​​for this column. How do I declare a column to achieve this?

  1. Assuming I have the same expression as above, how do I enter a blank value. I mean, if the column was of type varchar, I would enter the empty value as ". But since it is a date type, I get an error when I enter the value as" ". How do I enter an empty value for the pollDate column?
+2


a source to share


3 answers


MySQL has extremely limited support for defaults. In general, this must be a constant expression of type '0'

or '(none)'

, and functions are not allowed. The only exception I know of is that you can set the CURRENT_TIMESTAMP

default for a column TIMESTAMP

; however, this is not available for columns DATE

or DATETIME

. Unfortunately, it TIMESTAMP

has a smaller range and contains time information.

So your options are:

  • Do this in your client application by setting NOW () as the value in your inserts.
  • Write trigger:


...

CREATE TRIGGER pollOfTheWeek_trg BEFORE INSERT ON pollOfTheWeek 
    FOR EACH ROW SET NEW.pollDate = NOW();

As for null values, I just used NULL. Why use blank lines?

+1


a source


You need to create a trigger:

CREATE TRIGGER triggerName
BEFORE INSERT ON yourTable
FOR EACH ROW
SET NEW.date = coalesce(NEW.date, curdate());


An empty value will be null

.

+2


a source


there is no way to automatically set a column DATE

to the current date / time in mysql. TIMESTAMP

can be automatically set to the current time. alternatively, just pass NOW()

as a parameter on insert:

INSERT INTO pollOfTheWeek (name, pollDate) VALUES ('someName', NOW());

in regards to your other question, you probably want to allow NULL values ​​in the date column and set them to NULL if no date is provided.

+1


a source






More articles:


All Articles