Date and time types

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;