We help IT Professionals succeed at work.

MySQL: Insert Time and Date

hankknight
hankknight asked
on
I want to AUTOMATICALLY insert the current time and the current date.
CREATE TABLE IF NOT EXISTS `billing` (
  `account_id` mediumint(9) NOT NULL,
  `transaction_id` int(11) NOT NULL,
  `date` date NOT NULL DEFAULT CURRENT_DATE,
  `time` time NOT NULL DEFAULT CURRENT_TIME,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `debit` decimal(8, 2) NOT NULL,
  `credit` decimal(8, 2) NOT NULL,
  `memo` varchar(512) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=ascii;

Open in new window

Comment
Watch Question

ioboxWeb Architect

Commented:
if you pass null values to the insert query they will be inserted in auto...
ioboxWeb Architect
Commented:
sorry too short...

  `date` date NOT NULL DEFAULT CURRENT_DATE,
  `time` time NOT NULL DEFAULT CURRENT_TIME,

here u are saying that if value null insert or update with current_date and current_time

cheers.
Commented:
current_date = now()
current_time = now()

Author

Commented:
This does not work:
-- phpMyAdmin SQL Dump
-- version 3.1.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 11, 2010 at 05:31 PM
-- Server version: 5.1.30
-- PHP Version: 5.2.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `bb`
--

-- --------------------------------------------------------

--
-- Table structure for table `billing`
--


CREATE TABLE IF NOT EXISTS `billing` (
  `account_id` mediumint(9) NOT NULL,
  `transaction_id` int(11) NOT NULL,
  `date` date NOT NULL DEFAULT CURRENT_DATE,
  `time` time NOT NULL DEFAULT CURRENT_TIME,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `debit` decimal(8, 2) NOT NULL,
  `credit` decimal(8, 2) NOT NULL,
  `memo` varchar(512) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=ascii;

--
-- Dumping data for table `billing`
--

Open in new window

Author

Commented:
This does not work either.  What am I doing wrong?
-- phpMyAdmin SQL Dump
-- version 3.1.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 11, 2010 at 05:31 PM
-- Server version: 5.1.30
-- PHP Version: 5.2.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `bb`
--

-- --------------------------------------------------------

--
-- Table structure for table `billing`
--


CREATE TABLE IF NOT EXISTS `billing` (
  `account_id` mediumint(9) NOT NULL,
  `transaction_id` int(11) NOT NULL,
  `date` date NOT NULL DEFAULT current_date = now(),
  `time` time NOT NULL DEFAULT current_date = now(),
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `debit` decimal(8, 2) NOT NULL,
  `credit` decimal(8, 2) NOT NULL,
  `memo` varchar(512) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=ascii;

--
-- Dumping data for table `billing`
--

Open in new window

Commented:
`date` date NOT NULL DEFAULT CURDATE()
`time` time NOT NULL DEFAULT CURTIME()

Author

Commented:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURDATE(), `time` time NOT NULL DEFAULT CURTIME(), `timestamp` timestamp N' at line 4
CREATE TABLE IF NOT EXISTS `billing` (
`account_id` mediumint( 9 ) NOT NULL ,
`transaction_id` int( 11 ) NOT NULL ,
`date` date NOT NULL DEFAULTCURDATE( ) ,
`time` time NOT NULL DEFAULTCURTIME( ) ,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`debit` decimal( 8, 2 ) NOT NULL ,
`credit` decimal( 8, 2 ) NOT NULL ,
`memo` varchar( 512 ) NOT NULL

Open in new window

ioboxWeb Architect

Commented:
you are just creating the table billing with it, not inserting anything in it...


before dump if you need data you have to insert data...
-- phpMyAdmin SQL Dump
-- version 3.1.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 11, 2010 at 05:31 PM
-- Server version: 5.1.30
-- PHP Version: 5.2.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `bb`
--

-- --------------------------------------------------------

--
-- Table structure for table `billing`
--


CREATE TABLE IF NOT EXISTS `billing` (
  `account_id` mediumint(9) NOT NULL,
  `transaction_id` int(11) NOT NULL,
  `date` date NOT NULL DEFAULT current_date,
  `time` time NOT NULL DEFAULT current_date,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `debit` decimal(8, 2) NOT NULL,
  `credit` decimal(8, 2) NOT NULL,
  `memo` varchar(512) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=ascii;


INSERT INTO billing (account_id, transaction_id, debit, credit, memo)
values (1, 2, 123456789.10, 1234.12, 'memo test');

--
-- Dumping data for table `billing`
--

Open in new window

Author

Commented:
That code still gives an error!
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Hey Hank,

You can't set the default value for a TIME or DATE field to anything other than a constant value - Not CURDATE, CURTIME, CURRENT_TIME or CURRENT_DATE.

You have several options.

1. Drop the Date and Time field completely - You already have a timestamp field with a DEFAULT value of CURRENT_TIMESTAMP - you can extract the Data and Time from that. It will be set to the current timestamp when you create the record.

2. Set the values for Date and Time when you insert a record:

INSERT INTO billing (`account_id`, `transaction_id`, `date`, `time`, `debit`, `credit`, `memo`) VALUES (123,456, CURRENT_DATE,CURRENT_TIME,12.00,2.00,"Your memo data");

3. Create a trigger that will automatically set the date and time field for ya.

CREATE TRIGGER SetMyDefaultDateAndTime
  BEFORE INSERT ON billing
  FOR EACH ROW
    SET NEW.date = CURDATE(), NEW.time = CURTIME();