Insert data into mysql, in a timestamp field

Asked

Viewed 3,273 times

2

How do I enter information in a field of this type? The name of the field is duration, I would like an example..

mysql timestamp

  • Does that help you? give a look. [http://stackoverflow.com/questions/18092303/how-to-insert-custom-date-into-mysql-timestamp-field][1]

1 answer

5


Two syntaxes:

INSERT INTO meubanco.minhatabela ( duracao ) VALUES ( '2016-04-29 00:00:00' )

Or

INSERT INTO meubanco.minhatabela SET duracao = '2016-04-29 00:00:00'

(I hope your field is duracao, and not duração, to avoid problems)

The important thing is to pass the date as string, in format ano-mes-dia hora:minuto:segundo.centesimos

Ideally you will use 4 digits for the year, and 2 for all other fields, but if you pass in another format, in most cases Mysql makes one best Guess and rounds values or expands as needed.

Some remarks:

  • Years with two digits have the base date in 1970. Thus, values of 00 to 69 are converted to 2000 to 2069, and values of 70 to 99 for halftime 1970 to 1999 respectively.

  • To enter the current moment, according to the DB server, you can use CURRENT_TIMESTAMP or NOW();

  • If your column is the first TIMESTAMP table, by default Mysql inserts the timestamp current if field is omitted, or if explicitly provided NULL and the column specified as NOT NULL;

  • When inserting fields with the Timezone Mysql configured for something other than UTC, there is an internal conversion. Therefore, if you insert a date and change the Timezone, the date returned will be different (will always be relative to UTC).


More details in the manual (in English):

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-types.html


Inserting dates in DD/MM/YYYY format

Ideally, an application should provide the dates for Mysql already in the right format, to avoid conversions, but if you need a pre-conversion for insertion, you can do this:

INSERT INTO meubanco.minhatabela ( duracao ) VALUES (
   STR_TO_DATE('29/04/2016','%d/%m/%Y')
)

Similarly, to include the time:

INSERT INTO meubanco.minhatabela ( duracao ) VALUES (
   STR_TO_DATE('29/04/2016 13:49:20','%d/%m/%Y h:%i:%s')
)

More details of the fields and other conversion functions in the manual:

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

Browser other questions tagged mysql timestamp

You are not signed in. Login or sign up in order to post.