Date/Time/TIMESTAMP Data Types

• Date
  • Reference
  • Format: "yyyy-mm-dd"
  • Any character may be used as a delimiter
  • SELECT DATE ("2015/02/24");
  • No delimiters work, but not exactly as expected.
  • It will deal with '02-02-14"

  •  
    INSERT INTO FARM 
        (Name, Sex, DatePurchased) VALUES ("test","M","02-02-14");
              

  • The man page says
    • Year values in the range 70-99 are converted to 1970-1999.
    • Year values in the range 00-69 are converted to 2000-2069.
  • Invalid dates are turned to ZERO

    INSERT INTO FARM 
         (Name, Sex, DatePurchased) 
         VALUES 
         ("test","M","02-02-34");
    	  

  • Dates can be null

    INSERT INTO FARM 
         (Name, Sex, DatePurchased)
         VALUES 
         ("test","M",NULL);
    	  

  • But much of this depends on the local MySQL configuration
    • NO_ZERO_DATE
    • ALOOW_INVALID_DATES
• There are a bunch of Date and Time Functions
  • DAY, MONTH, YEAR

    SELECT MONTH(DatePurchased)
        FROM FARM;
    	  

  • CURDATE() for the current date
    • SELECT CURDATE();
    • Also CURRENT_DATE() and CURRENT_DATE
  • DAYOFWEEK(date), DAYOFMONTH(date), DAYOFYEAR(date)
  • DAYNAME(date)
  • DATEDIFF(date1,date2)
  • FROM_DAYS(int)
  • SELECT FROM_DAYS(DATEDIFF(CURDATE(),"1995-09-22"));
  • LAST_DAY('date') returns the last day of the month
  • SELECT LAST_DAY('2016-02-01');
  • QUARTER(date) returns the quarter of the year.
• TIME
  • Format: 'HH:MM:SS'
    • "HH:MM" is acceptable, and implies seconds 00
    • "12" is "00:00:12"
    • "1112" is "00:11:12"
  • MANY functions as well, see previous section.
• TIMESTAMP or DATETIME
  • 'YYYY-MM-DD HH:MM:SS'
  • Can be set by default to current date.
  • Many functions to convert.

    • SELECT NOW();
      SELECT DATE(NOW());
      SELECT TIME(NOW());
      SELECT DAYNAME(NOW());