Automatically insert Current Date and Time in MySQL table #Part – 2.2

Swashata30
This entry is part 3 of 5 in the series MySQL DataTypes
wp-content/uploads/2012/06/mysql-datatype-series-logo.png
MySQL DataTypes

MySQL-date-and-time-function Previously we have discussed widely on the Date and Time datatypes of MySQL. Date and Time both are important to keep exact record of inserted data in a particular table. So, the usage of Date/Time is wide in MySQL and PHP. Today we shall see how we can automatically insert current Date and Time using MySQL queries.

Basically current date and time can be inserted into a properly defined MySQL table using 3 simple functions… CURDATE(), CURTIME(), NOW(). With the proper usage of these functions you can easily play with date and time in MySQL. Below we have elaborated how to use them and where to use them…

Creating a table for the demonstration:

For the ease of this discussion, we are first going to make a MySQL table in the following manner.

mySQL function dateTime date time year timeStamp
… … … … … …

Obviously DATETIME, DATE, TIME etc are set to its relevant Date and Time datatypes. Now to make this table we open up the MySQL terminal and enter the following command:

CREATE TABLE auto_ins
(
MySQL_Function VARCHAR(30),
DateTime DATETIME,
Date DATE,
Time TIME,
Year YEAR,
TimeStamp TIMESTAMP
);

creating table Click on the picture to enlarge

Note that here we have used a custom database date_time to store this table.

creating db

CURDATE() MySQL function to automatically insert date:

This command is used to insert current date (with respect to the execution of this command) into a MySQL table. It can be applied on either of the Four (4) MySQL Datatypes => DATE, DATETIME, YEAR & TIMESTAMP. But in all the cases only the date will be recorded on the field. Rest data (if present) will be stored as its Zero Value (Zero value has been discussed on the previous article). On the YEAR field, only Year value will be snatched from the date.

Format:

YYYY-MM-DD

Demonstration:

Simply run the following two sets of commands on your MySQL terminal or Command Prompt Window…

INSERT INTO auto_ins
(MySQL_Function, DateTime, Date, Time, Year, TimeStamp)
VALUES
(“CURDATE()”, CURDATE(), CURDATE(), CURDATE(), CURDATE(), CURDATE());
SELECT * FROM auto_ins;

The Following result will show up:CURDATE demo

Click on the picture to enlarge

CURTIME() MySQL function to automatically insert Time:

CURTIME() is used to automatically insert time on MySQL table field. Unlike CURDATE()  it can be used only with TIME datatype fields. Obviously on errors, the field will return its Zero Value.

Format:

HH:mm:SS

Demonstration:

Run the following code on your MySQL terminal. The result will clear your concept about the usage of CURTIME().

INSERT INTO auto_ins
(MySQL_Function, DateTime, Date, Time, Year, TimeStamp)
VALUES
(“CURTIME()”, CURTIME(), CURTIME(), CURTIME(), CURTIME(), CURTIME());
SELECT * FROM auto_ins;

CURTIME demo

Click on the picture to enlarge

Note the error crept in the YEAR field. So be careful while using this function with these types of fields.

NOW() MySQL function to automatically insert Date and Time:

NOW() is used to insert the current date and time in the MySQL table. All Fields with datatypes DATETIME, DATE, TIME & TIMESTAMP works good with this function.

YYYY-MM-DD HH:mm:SS

Demonstration:

Following code shows the usage of NOW()

INSERT INTO auto_ins
(MySQL_Function, DateTime, Date, Time, Year, TimeStamp)
VALUES
(“NOW()”, NOW(), NOW(), NOW(), NOW(), NOW());
SELECT * FROM auto_ins;

This will result like:-

NOW demoClick on the picture to enlarge

UPDATE:2011-12-17

YEAR() MySQL function to extract year from a date or time stamp:

YEAR() is used to extract a 4 digit year from a date or time stamp. It’s usaged is something like

SELECT YEAR(CURDATE());

This outputs 2011. Similarly

SELECT YEAR('2009-11-30');

will output 2009.

Thanks to madhu for enlightening the fact that, simply using CURDATE() on a YEAR field will generate warning.

Overview on the MySQL Table:

This will be MySQL auto_ins table we have made through this tutorial:

mySQL function dateTime date time year timeStamp
CURDATE() 2010-02-02 00:00:00 2010-02-02 00:00:00 2010 2010-02-02 00:00:00
CURTIME() 0000-00-00 00:00:00 0000-00-00 23:09:53 2023 0000-00-00 00:00:00
NOW() 2010-02-02 23:34:48 2010-02-02 23:34:48 2010 2010-02-02 23:34:48

So from this table we can conclude the following usage on the MySQL date time functions:

MySQL date Time functions usable field datatype remark
CURDATE() DATE, DATETIME, TIMESTAMP, YEAR(should be wrapped inside YEAR())
 Can store only the date value on DATETIME and TIMESTAMP. The Time will be set to the ZERO VALUE
CURTIME() TIME Returns error while used in fields with datatype YEAR
NOW() DATE, TIME, DATETIME, TIMESTAMP, YEAR(Should be wrapped inside YEAR())
 Can be used to all date/time datatype fields. It is synonymous to LOCALTIMESTAMP() LOCALTIME()CURRENT_TIMESTAMP()etc

That was the fundamentals of MySQL date/time functions. But there are more. You can read about them here at the official MySQL documentaries. I hope you have enjoyed this article. Do give you feedback. Also we love questions… So if you have one, throw it through your comment.

Series Navigation

« MySQL date and time DataTypes Overview: DATE, TIME, DATETIME, TIMESTAMP, YEAR & Zero Values #Part2.1MySQL: Working with date time arithmetic #Part 2.3.1 »

30 comments

  1. Omer Donn

    I happen to be seeking seeking all around for this sort of information. Will you publish some a lot more in long term?

  4. Pingback: Tweets that mention Automatically insert Current Date and Time in MySQL table #Part – 2.2 | InTechgrity -- Topsy.com

  5. madhu

    Dear Swashata,

    Really good things which you all explained. Its really helps like myself as beginners. Well and appreciated that.

    My problem is When I am tried to use this now() function for the year, its doesnt seems to be. Need to pick up only the year. But its throw me an error like “:Data truncated for column ‘year’ at row 1”

    I am trying to use the code for to gather the clients information. So I just make it as form and post the value into php and store into mysql. The rest of the fields are working great if I use for now() but data type if i use year its gives me an error like above.

    is that possible to give me the things how to truncate if I use the year data field ?

    Thanks in advance.

    madhu
    madhuramanathan@gmail.com

    • madhu

      Dear Swashata,

      Please reply to my mail as well. Sorry forgotten to check the notify me thread.

      thanks.

    • Swashata Post author

      For that, you have to nest the CURDATE() inside YEAR() function. Use something like this

      INSERT INTO table_name (year_field) VALUE (YEAR(CURDATE());

      Or

      INSERT INTO table_name (year_field) VALUE (YEAR('2009-11-30'));
      • G.Nirmala

        Dear Swashata ..
        Im Nirmala..
        how can i update the modified date in mysql directly..for this i used to GETDATE() function but,it is updated 0000:00:00 00:00:00 like this…
        Thanks in advance…

  6. Pingback: MySQL date time arithmetic using various inbuilt functions | InTechgrity

  7. satish

    sir i’m developping a project of offline parking billing system in php which stores customer arrival time inbuiltly and customer departure time and calculates time difference in hour by using current date and time and provide bill as per hour but i dont know how to save current date and time in mysql database and how to calculate hours difference between current ‘date and time’ and ‘old date and time’ please help me……please

  8. FOXY

    Hey great blog. I’m currently making an item in my mysql table like this:

    ALTER TABLE `error_log` ADD `date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `id`

    But I get an error msg:
    #1067 – Invalid default value for ‘date’

    If you could be of any help, please shoot me an email or comment in any of my blog posts!

    Thanks for your time… FOXY

    • Swashata Post author

      Hi, sorry for the delayed reply. Was out on a vacation :). Anyway, you use it like 

      ALTER TABLE `error_log` ADD `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `id`

      if you wish to update the value on every insert/update operation on the table. There is no way you can pass any mysql function like NOW() to the default value. So this is perhaps a limitation. I prefer to use the server side script to update the datetime column to current date-time.

  10. vivek

    insert query is not working,i got error 1265,Data Truncated for column Year at row 1

    the query is
    INSERT INTO auto_ins
    (MySQL_Function, DateTime, Date, Time, Year, TimeStamp)
    VALUES
    (“CURDATE()”, CURDATE(), CURDATE(), CURDATE(), CURDATE(), CURDATE());

  11. pratik

    i am working with php project in that i have problem:”in date and time column in databse displays 0000-00-00 00:00:00″
    how can i overcome from this problem please tell me
    thanks

  12. Arslan

    It is very beneficial tutorial thanks a lot =)

  13. imas

    hii. i want to insert current datetime to database mysql with php. i used date(‘Y-m-d H:i:s’), but when i checked on the database, it be 0000-00-00 00:00:00 .. Hope you could help me.

    thanks 🙂

    • Swashata Post author

      The functions are bit different in MySQL than PHP. If you are using MySQL to insert the date then follow this article.

      Or in PHP, it would be something like this:

      $db->query("INSERT INTO table(date) VALUES ('" . $db->escape(date('Y-m-d H:i:s')) . "'");
  14. Pradeep kumar

    Hi,

    I am working with cakephp, Actually i want to save current date and time in the mysql table, how can i write code for that.
    Please guide me.

    Thanks

  15. Prashant

    this code is working fine for MySql database
    how can we use same for oracleXE database?
    pls Do reply fast

  16. raney

    hi there,we are making an Attendance Monitoring System of our school personnel and we want that as they log in the date,time and calculations of hours will automatically recorded to database.

  17. raney

    we are using php mysql sir…your help is highly appreciated…thank you and God bless.

  18. Beto

    Dear Swashata, is there any way to insert data automatically to a mysql table?

    i mean, insert daily data without a submit button or something like that.

  20. Preethi

    i want to automatically delete a particular table from database after certain period of time, how do i do it?

  21. sabi

    hi swashata, how can automatically insert data into database in a particular time wise.

Comments are closed.