Date and time types

From SQLZOO
Revision as of 12:41, 17 July 2012 by Marek (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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