Link to home
Start Free TrialLog in
Avatar of hankknight
hankknightFlag for Canada

asked on

MySQL: Insert Time and Date

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

Avatar of iobox
iobox
Flag of Italy image

if you pass null values to the insert query they will be inserted in auto...
SOLUTION
Avatar of iobox
iobox
Flag of Italy image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of hankknight

ASKER

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

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

Avatar of damzie
damzie

`date` date NOT NULL DEFAULT CURDATE()
`time` time NOT NULL DEFAULT CURTIME()
#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

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

That code still gives an error!
ASKER CERTIFIED SOLUTION
Avatar of Chris Stanyon
Chris Stanyon
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial