Monthly Archives: May 2012

SQL

SQL DATEDIFF()

SQL  DATEDIFF() function returns the time between two dates.

Table – orders

+—–+—–+——-+———————+———————+
| oid | uid | items | date                | delivery_date       |
+—–+—–+——-+———————+———————+
|   1 |   1 | AAAA  | 2007-05-16 00:00:00 | 2007-05-24 00:00:00 |
+—–+—–+——-+———————+———————+

Example

mysql> SELECT DATEDIFF(delivery_date,date) AS days FROM orders;
+——+
| days |
+——+
|    8 |
+——+
1 row in set (0.03 sec)

SQL

SQL DATE_SUB()

SQL DATE_SUB() function subtracts a specified time interval from a date.

Table – orders

+—–+—–+——-+———————+
| oid | uid | items | date                |
+—–+—–+——-+———————+
|   1 |   1 | AAAA  | 2007-05-16 00:00:00 |
+—–+—–+——-+———————+

Example

mysql> SELECT DATE_SUB(date, INTERVAL 4 DAY) FROM orders;
+——————————–+
| DATE_SUB(date, INTERVAL 4 DAY) |
+——————————–+
| 20007-05-12 00:00:00            |
+——————————–+
1 row in set (0.00 sec)

Unit

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR

SQL

SQL DATE_ADD()

SQL DATE_ADD() function adds a specified time interval to a date.

Table – orders

+—–+—–+——-+———————+
| oid | uid | items | date                |
+—–+—–+——-+———————+
|   1 |   1 | AAAA  | 2007-05-16 00:00:00 |
+—–+—–+——-+———————+

Example

mysql> SELECT DATE_ADD(date, INTERVAL 4 DAY) FROM orders;
+——————————–+
| DATE_ADD(date, INTERVAL 4 DAY) |
+——————————–+
| 2007-05-20 00:00:00            |
+——————————–+
1 row in set (0.01 sec)

Unit

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR

SQL

SQL EXTRACT()

SQL EXTRACT() return a single part of a date/time(likes year, month, day, hour, minute)

Table – orders

+—–+—–+——-+———————+
| oid | uid | items | date                |
+—–+—–+——-+———————+
|   1 |   1 | AAAA  | 2007-05-16 00:00:00 |
+—–+—–+——-+———————+

Example

mysql> SELECT EXTRACT(YEAR FROM date) FROM orders;
+————————-+
| EXTRACT(YEAR FROM date) |
+————————-+
|                    2007 |
+————————-+
1 row in set (0.00 sec)

Unit

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR

SQL

SQL DATE()

SQL DATE() function extracts the date part of a date.

Table  – orders

+—–+—–+——-+———————+
| oid | uid | items | date                |
+—–+—–+——-+———————+
|   1 |   1 | AAAA  | 2007-05-16 00:00:00 |
+—–+—–+——-+———————+

Example

mysql> SELECT DATE(date) as bill_date from orders ;
+————+
| bill_date  |
+————+
| 2007-05-16 |
+————+
1 row in set (0.00 sec)

SQL

MySQL Date Functions

SQL support many functions with which we can manage the dates efectively

Below given are the date functions is MySql

  • NOW()- Returns the current date and time
  • CURDATE()- Returns the current date
  • CURTIME()- Returns the current time
  • DATE()- Extracts the date part of a date or date/time expression
  • EXTRACT()- Returns a single part of a date/time
  • DATE_ADD()- Adds a specified time interval to a date
  • DATE_SUB() -Subtracts a specified time interval from a date
  • DATEDIFF()- Returns the number of days between two dates
  • DATE_FORMAT()- Displays date/time data in different formats
SQL

SQL VIEW

A view is a virtual table based on the result-set of an SQL statement .

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

We can add  fetch data from view using sql statements/function as if the data were coming from one single table.

Syntax

CREATE VIEW view_name AS SELECT * FROM table_name ;

Example

mysql> CREATE VIEW view_users AS SELECT * FROM users ;
Query OK, 0 rows affected (0.13 sec)