Function handles DATE / TIME in SQL - Part 1

In this article, Quantrimang lists all the important functions used to handle Date / Time in SQL. The list is based on MySQL RDBMS.

Date / time processing functions

NO ADDITIONAL DESCRIPTION 1 ADDDATE () Add a time period in date 2 ADDTIME () Add a time period to time 3 CONVERT_TZ () Time zone switch 4 CURDATE () Returns the current date 5 CURRENT_DATE () Returns date current 6 CURRENT_TIME () Returns the current time 7 CURRENT_TIMESTAMP () Returns the current date and time 8 CURTIME () Returns the current time 9 DATE_ADD () Plus time 10 DATE_FORMAT () Time value format 11th time DATE_SUB () Except time 12 DATE () Returns the date part of the time expression 13 DATEDIFF () Returns the difference between two time values ​​14 DAY () Returns the date order of the month (from 0 to 31) 15 DAYNAME () Returns the name of the day of the week 16 DAYOFMONTH () Returns the date of the month (from 0 to 31) 17 DAYOFWEEK () Returns the number of days of the week (1 = Sunday, 2 = Monday. . 7 = Saturday) 18 DAYOFYEAR () Returns the date of the year (from 1 to 366) 19 EXTRACT Extracts the time value from the original expression 20 FROM_DAYS () Converts a number to day 21 FROM_UNIXTIME () Returns the date of the representation for the original Unixtime parameter 22 HOUR () Returns the hour part from the time expression 23 LAST_DAY () Returns the corresponding value for the last day of the 24th month LOCALTIME () Returns the current date and time 25 LOCALTIMESTAMP () Returns About the current date and time 26 MAKEDATE () Returns a date with parameters passed into 27 MAKETIME () Returns the time value with the passed parameters 28 MICROSECOND () Returns the microsecond from the original expression 29 MINUTE ( ) Returns the minute value from the time expression 30 MONTH () Returns the month value from the time expression 31 MONTHNAME () Returns the month name from the time expression gi an 32 NOW () Returns the current date and time 33 PERIOD_ADD () Adds a time period for the month of the year 34 PERIOD_DIFF () Returns the number of months between periods 35 QUARTER () Returns the quarter value from the time expression 36 SEC_TO_TIME () Converts the number of seconds to the format 'HH: MM: SS' 37 SECOND () Returns the second value from the time expression 38 STR_TO_DATE () Converts a string to a 39 day SUBDATE () Except one time interval from date passed to 40 SUBTIME () Except for two time periods 41 SYSDATE () Returns the current date and time 42 TIME_FORMAT () Time value format 43 TIME_TO_SEC () Returns the number of seconds converted from the parameter initial 44 TIME () Returns the time value from the original expression 45 TIMEDIFF () Returns the difference between two time values ​​46 TIMESTAMP () Returns the datetime expression 47 TIMESTAMPADD () Adds the time interval specified in the initial parameter 48 TIMESTAMPDIFF () Returns an integer representing the time difference between two expressions 49 TO_DAYS () Returns the number of days between the date value and year 0 50 UNIX_TIMESTAMP () Returns the number of seconds under Unix timestamp from the original expression 51 UTC_DATE () Returns the current UTC date 52 UTC_TIME () Returns the current UTC time 53 UTC_TIMESTAMP () Returns the current UTC date and time 54 WEEKDAY () Returns the weekday index (0 = Monday, 1 = Tuesday . 6 = Sunday) 55 WEEKOFYEAR () Returns the week number in the year of the time expression 56 YEAR () Returns The corresponding year of the original parameter

Date data types in SQL

MySQL comes with data types to store date or date / time values ​​in the database:

1. DATE - YYYY-MM-DD format
2. DATETIME - format: YYYY-MM-DD HH: MI: SS
3. TIMESTAMP - format: YYYY-MM-DD HH: MI: SS
4. YEAR - YYYY or YY format

With:

1. Y is the year
2. M is the month
3. D is the date
4. H is the hour
5. MM is the minute
6. SS is seconds

YYYY is the year saved as a four-digit number, YY is the year saved as a two-digit number; MM is saved as a two-digit month, for example July will be 07):

In the next section, Quan trimang will work with you to learn how to use all of the above functions with the syntax and specific examples, please remember to follow.

Previous article: Operator representing WILDCARD in SQL

Next lesson: Function handling DATE / TIME in SQL - Part 2