hankknight
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;
if you pass null values to the insert query they will be inserted in auto...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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`
--
ASKER
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`
--
`date` date NOT NULL DEFAULT CURDATE()
`time` time NOT NULL DEFAULT CURTIME()
`time` time NOT NULL DEFAULT CURTIME()
ASKER
#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
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...
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`
--
ASKER
That code still gives an error!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.