Current Date and Time

Back to: Structured Query Language (SQL)

For this short topic we will create a new table, run some demonstrative commands, then delete the table.

CREATE TABLE date_stuff(
     my_date DATE,
     my_time TIME,
     my_datetime DATETIME
);

Recall that this just creates the table headings – there is no data yet.

SELECT * FROM date_stuff;

We can add date information into our database table using the built-in functions of CURRENT_DATE() and CURRENT_TIME(). There is also NOW(). These will insert a time stamp for the date, time and datetime of the moment the function is invoked.

INSERT INTO date_stuff
VALUES(CURRENT_DATE(), CURRENT_TIME(), NOW());
SELECT * FROM date_stuff;

Outputs the sad time and date that your dedicated teacher is working on this lesson (nearly 4am on a Monday morning) – but this stuff doesn’t write itself:

If we need to do some calculations about the date from now, for instance, make the Due Date in one week, we can use:

INSERT INTO date_stuff
VALUES(CURRENT_DATE() + 7, CURRENT_TIME(), NOW());
SELECT * FROM date_stuff;

DROP TABLE date_stuff;