Insert the current date and time into an SQL

Insert the current date and time into an SQL database

Content

1 Objective 2 Scenario 3 Method 4 Variations

Tested with PostgreSQL

Debian (Etch, Lenny, Squeeze)

Fedora (14)

Ubuntu (Hardy, Intrepid, Jaunty, Karmic, Lucid, Maverick, Natty)

Tested with MySQL

Debian (Etch, Lenny, Squeeze)

Fedora (14)

Ubuntu (Hardy, Intrepid, Jaunty, Karmic, Lucid, Maverick, Natty)

Objective

To insert the current date and time into an SQL database.

Scenario

Suppose that a database has a table for recording events, and each event has a timestamp:

CREATE TABLE events (

  ts TIMESTAMP,

  description VARCHAR(80));

Method

Use the standard SQL function CURRENT_TIMESTAMP to obtain the current date and time:

INSERT INTO events (ts,description) VALUES (CURRENT_TIMESTAMP,'disc full');

The value returned by CURRENT_TIMESTAMP includes the timezone, which is dropped in the above example, but which would be kept if ts were of type TIMESTAMP WITH TIME ZONE.

Variations

To obtain just the time, use the function CURRENT_TIME.

To obtain just the date, use the function CURRENT_DATE.