Difference between revisions of "Date and time types"

From SQLZOO
Jump to: navigation, search
(Created page with "Date and time types. <div class='ht'> <div class=params>schema:scott</div> <div> The SQL standard permits three types. DATE, TIME and TIMESTAMP. A TIMESTAMP includes a date an...")
 
 
Line 59: Line 59:
 
SELECT * FROM t_dttest;
 
SELECT * FROM t_dttest;
 
</source>
 
</source>
<source lang='sql' class='def'>CREATE TABLE t_dttest(
+
<source lang='sql' class='def e-mysql'>CREATE TABLE t_dttest(
 
   a DATE,
 
   a DATE,
 
   b TIME,
 
   b TIME,

Latest revision as of 11:42, 17 July 2012

Date and time types.

schema:scott

The SQL standard permits three types. DATE, TIME and TIMESTAMP. A TIMESTAMP includes a date and a time. Several engines use the word TIMESTAMP for a different purpose.

DROP TABLE t_dttest
CREATE TABLE t_peep
 (id INTEGER PRIMARY KEY
 ,name VARCHAR(50))
CREATE TABLE t_dttest(
  a DATE,
  c TIMESTAMP);
 
INSERT INTO t_dttest VALUES (
  DATE '1962-05-20',
  TIMESTAMP '1962-05-20 10:32:16');
 
SELECT * FROM t_dttest;
CREATE TABLE t_dttest(
  a DATE,
  b TIME,
  c TIMESTAMP);
 
INSERT INTO t_dttest VALUES (
  '1962-05-20',
  '10:32:16',
  '1962-05-20 10:32:16');
 
SELECT * FROM t_dttest;
CREATE TABLE t_dttest(
  a DATETIME,
  b DATETIME,
  c DATETIME);
 
INSERT INTO t_dttest VALUES (
  '1962-05-20',
  '10:32:16',
  '1962-05-20 10:32:16');
 
SELECT * FROM t_dttest;
CREATE TABLE t_dttest(
  a DATE,
  b TIME,
  c DATETIME);
 
INSERT INTO t_dttest VALUES (
  '1962-05-20',
  '10:32:16',
  '1962-05-20 10:32:16');
 
SELECT * FROM t_dttest;
CREATE TABLE t_dttest(
  a DATE,
  b TIME,
  c DATETIME);
 
INSERT INTO t_dttest VALUES (
  DATE '1962-05-20',
  TIME '10:32:16',
  TIMESTAMP '1962-05-20 10:32:16');
 
SELECT * FROM t_dttest;
CREATE TABLE t_dttest(
  a DATE,
  b TIME,
  c TIMESTAMP);
 
INSERT INTO t_dttest VALUES (
  DATE '1962-05-20',
  TIME '10:32:16',
  TIMESTAMP '1962-05-20 10:32:16');
 
SELECT * FROM t_dttest;
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense