sql command

Database SQL – Date Function คือ ฟังก์ชันการใช้งานข้อมูลวันที่และเวลา

Database SQL – Date Function

การใช้งาน Date function จะมีด้วยกันหลายรูปแบบ ผู้ใช้งานสามารถเลือกใช้ function ที่เหมือนสมกับความต้องการได้ โดยส่วนมาก database แบบ RDBMS จะรองรับ function date ตามข้อมูลดังนี้

Date Function Expression Detail
  ADDDATE()   ADDDATE(expr,days)   เพิ่มจำนวนวัน
  ADDTIME()   ADDTIME(expr1,expr2)   เพิ่มจำนวนเวลา
  CONVERT_TZ()   CONVERT_TZ(dt,from_tz,to_tz)   เปลี่ยน timezone (GMT)
  CURDATE()   CURDATE()   แสดงวันปัจจุบัน
  CURRENT_DATE()   CURRENT_DATE()   แสดงวันปัจจุบัน
  CURRENT_TIME()   CURRENT_TIME()   แสดงเวลาปัจจุบัน
  CURRENT_TIMESTAMP()   CURRENT_TIMESTAMP()   แสดงวันและเวลาปัจจุบัน
  CURTIME()   CURTIME()   แสดงเวลาปัจจุบัน
  DATE_ADD()   DATE_ADD(date,INTERVAL expr unit)   เพิ่มจำนวนวัน
  DATE_FORMAT()   DATE_FORMAT(date,format)   กำหนดรูปแบบการแสดงผลวันเวลา
  DATE_SUB()   DATE_SUB(date,INTERVAL expr unit)   ลดจำนวนวัน
  DATE()   DATE(expr)   แสดงเฉพาะส่วนของวันที่
  DATEDIFF()   DATEDIFF(expr1,expr2)   คำนวณระยะห่างของวัน
  DAY()   DAY(date)   แสดงวันที่ของเดือน 1-31
  DAYNAME()   DAYNAME(date)   แสดงชื่อวันของสัปดาห์
  DAYOFMONTH()   DAYOFMONTH(date)   แสดงวันที่ของเดือน 1-31
  DAYOFWEEK()   DAYOFWEEK(date)   แสดงชื่อวันของสัปดาห์
  DAYOFYEAR()   DAYOFYEAR(date)   แสดงวันที่ของปี 1-366
  EXTRACT()   EXTRACT(unit FROM date)   แยกข้อมูลส่วนของวันเดือนปี
  FROM_DAYS()   FROM_DAYS(N)   วันที่ห่างจาก Gregorian calendar (1582)
  FROM_UNIXTIME()   FROM_UNIXTIME (unix_timestamp,format)   แสดงรูแบบวันที่แบบ Unix
  HOUR()   HOUR(time)   แยกข้อมูลส่วนของชั่วโมง
  LAST_DAY()   LAST_DAY(date)   แสดงวันสุดท้ายข้องเดือน (28,29,30,31)
  LOCALTIME()   LOCALTIME()   แสดงเวลาปัจจุบัน
  LOCALTIMESTAMP()   LOCALTIMESTAMP()   แสดงเวลาปัจจุบัน
  MAKEDATE()   MAKEDATE(year,dayofyear)   ใช้ day of year มาสร้าง วันเดือนปี
  MAKETIME()   MAKETIME(hour,minute,second)   สร้างเวลาจากตัวแปร ชั่วโมง,นาที,วินาที
  MICROSECOND()   MICROSECOND(expr)   แสดงข้อมูลเฉพาะ microsecond
  MINUTE()   MINUTE(time)   แสดงข้อมูลเฉพาะนาที
  MONTH()   MONTH(date)   แสดงข้อมูลเฉพาะเดือน
  MONTHNAME()   MONTHNAME(date)   แสดงข้อมูลชื่อเดือน
  NOW()   NOW()   แสดงวันและเวลาปัจจุบัน
  PERIOD_ADD()   PERIOD_ADD(P,N)   เพิ่มจำนวนเดือน (N) ใน (P) ด้วย format YYYYMM
  PERIOD_DIFF()   PERIOD_DIFF(P1,P2)   คำนวณระยะห่างของเดือน
  QUARTER()   QUARTER(date)   แสดงข้อมูล quarter (1-4)
  SEC_TO_TIME()   SEC_TO_TIME(seconds)   เปลี่ยนวินาทีเป็น format เวลา HH:MM:S
  SECOND()   SECOND(time)   แสดงข้อมูลเฉพาะวินาที
  STR_TO_DATE()   STR_TO_DATE(str,format)   เปลี่ยน string เป็น date format
  SUBDATE()   SUBDATE(expr,days)   ลดจำนวนวัน
  SUBTIME()   SUBTIME(expr1,expr2)   ลดจำนวนเวลา
  SYSDATE()   SYSDATE()   แสดงวันเวลาปัจจุบัน
  TIME_FORMAT()   TIME_FORMAT(time,format)   แสดงเวลาตาม format ที่ระบุ
  TIME_TO_SEC()   TIME_TO_SEC(time)   แปลงเวลาเป็นหน่วยวินาที (0-86400)
  TIME()   TIME(expr)   แสดงเฉพาะส่วนของเวลา
  TIMEDIFF()   TIMEDIFF(expr1,expr2)   คำนวนส่วนต่างของเวลา
  TIMESTAMP()   TIMESTAMP(expr)   แสดงวันเวลาตาม format
  TIMESTAMPADD()   TIMESTAMPADD (unit,interval,datetime_expr)   เพิ่มจำนวนเวลา
  TIMESTAMPDIFF()    TIMESTAMPDIFF (unit,datetime_expr1,datetime_expr2)   คำนวณส่วนต่างของวันเวลา
  TO_DAYS()   TO_DAYS(date)   แสดงจำนวนวัน ตั้งแต่ปีที่ 0
  UNIX_TIMESTAMP()   UNIX_TIMESTAMP(date)   แปลง epoch date หรือ UNIX timestamp
  UTC_DATE()   UTC_DATE()   แสดงวันที่ปัจจุบัน UTC
  UTC_TIME()   UTC_TIME()   แสดงเวลาปัจจุบัน UTC
  UTC_TIMESTAMP()   UTC_TIMESTAMP()   แสดงวันเวลาปัจจุบัน UTC
  WEEK()   WEEK(date[,mode])   แสดงเลขของสัปดาห์ในปี (1-53)
  WEEKDAY()   WEEKDAY(date)   แสดง weekday 0-6  (0 = วันจันทร์)
  WEEKOFYEAR()   WEEKOFYEAR(date)   แสดงเลขของสัปดาห์ในปี (1-53)
  YEAR()   YEAR(date)   แสดงข้อมูลปี
  YEARWEEK()   YEARWEEK(date)   แสดงข้อมูลปีและสัปดาห์

 

ตัวอย่าง DATE Function

ADDDATE

ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days), DATE_ADD(expr,days)

mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| DATE_ADD('1998-01-02', INTERVAL 31 DAY)                 |
+---------------------------------------------------------+
| 1998-02-02                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| ADDDATE('1998-01-02', INTERVAL 31 DAY)                  |
+---------------------------------------------------------+
| 1998-02-02                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

สำหรับ MySQL argument ที่ 2 ถ้าไม่กำหนดหน่วยจะเป็นวัน

mysql> SELECT ADDDATE('1998-01-02', 31);
+---------------------------------------------------------+
| DATE_ADD('1998-01-02', INTERVAL 31 DAY)                 |
+---------------------------------------------------------+
| 1998-02-02                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

ADDTIME

ADDTIME(expr1,expr2)

mysql> SELECT ADDTIME('1997-12-31 23:59:59.999999','1 1:1:1.000002');
+---------------------------------------------------------+
| DATE_ADD('1997-12-31 23:59:59.999999','1 1:1:1.000002') |
+---------------------------------------------------------+
| 1998-01-02 01:01:01.000001                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

CONVERT_TZ

CONVERT_TZ(dt,from_tz,to_tz)

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
+---------------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','GMT','MET')           |
+---------------------------------------------------------+
| 2004-01-01 13:00:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
+---------------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00')     |
+---------------------------------------------------------+
| 2004-01-01 22:00:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

CURDATE

CURDATE(), CURRENT_DATE()

mysql> SELECT CURDATE();
+---------------------------------------------------------+
| CURDATE()                                               |
+---------------------------------------------------------+
| 1997-12-15                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CURDATE() + 0;
+---------------------------------------------------------+
| CURDATE() + 0                                           |
+---------------------------------------------------------+
| 19971215                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

CURTIME

CURTIME(), CURRENT_TIME()

mysql> SELECT CURTIME();
+---------------------------------------------------------+
| CURTIME()                                               |
+---------------------------------------------------------+
| 23:50:26                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CURTIME() + 0;
+---------------------------------------------------------+
| CURTIME() + 0                                           |
+---------------------------------------------------------+
| 235026                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

DATE

DATE(expr)

mysql> SELECT DATE('2003-12-31 01:02:03');
+---------------------------------------------------------+
| DATE('2003-12-31 01:02:03')                             |
+---------------------------------------------------------+
|  2003-12-31                                             |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

DATEDIFF

DATEDIFF(expr1,expr2)

mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
+---------------------------------------------------------+
| DATEDIFF('1997-12-31 23:59:59','1997-12-30')            |
+---------------------------------------------------------+
| 1                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

DATE_ADD

DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)

Unit Expected exprFormat
  MICROSECOND   MICROSECONDS
  SECOND   SECONDS
  MINUTE   MINUTES
  HOUR   HOURS
  DAY   DAYS
  WEEK   WEEKS
  MONTH   MONTHS
  QUARTER   QUARTERS
  YEAR   YEARS
  SECOND_MICROSECOND   ‘SECONDS.MICROSECONDS’
  MINUTE_MICROSECOND   ‘MINUTES.MICROSECONDS’
  MINUTE_SECOND   ‘MINUTES:SECONDS’
  HOUR_MICROSECOND   ‘HOURS.MICROSECONDS’
  HOUR_SECOND   ‘HOURS:MINUTES:SECONDS’
  HOUR_MINUTE   ‘HOURS:MINUTES’
  DAY_MICROSECOND   ‘DAYS.MICROSECONDS’
  DAY_SECOND   ‘DAYS HOURS:MINUTES:SECONDS’
  DAY_MINUTE   ‘DAYS HOURS:MINUTES’
  DAY_HOUR   ‘DAYS HOURS’
  YEAR_MONTH   ‘YEARS-MONTHS’
mysql> SELECT DATE_ADD('1997-12-31 23:59:59', 
   -> INTERVAL '1:1' MINUTE_SECOND);
+---------------------------------------------------------+
| DATE_ADD('1997-12-31 23:59:59', INTERVAL...             |
+---------------------------------------------------------+
| 1998-01-01 00:01:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 HOUR);
+---------------------------------------------------------+
| DATE_ADD('1999-01-01', INTERVAL 1 HOUR)                 |
+---------------------------------------------------------+
| 1999-01-01 01:00:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

DATE_FORMAT

DATE_FORMAT(date,format)

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
+---------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y')          |
+---------------------------------------------------------+
| Saturday October 1997                                   |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00'
   -> '%H %k %I %r %T %S %w');
+---------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00.......                 |
+---------------------------------------------------------+
|  22 22 10 10:23:00 PM 22:23:00 00 6                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

DAY

DAY(date), DAYOFMONTH(date)

mysql> SELECT DAYOFMONTH('1998-02-03');
+---------------------------------------------------------+
| DAYOFMONTH('1998-02-03')                                |
+---------------------------------------------------------+
| 3                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

DAYNAME

DAYNAME(date)

mysql> SELECT DAYNAME('1998-02-05');
+---------------------------------------------------------+
| DAYNAME('1998-02-05')                                   |
+---------------------------------------------------------+
| Thursday                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

DAYOFWEEK

DAYOFWEEK(date)

mysql> SELECT DAYOFWEEK('1998-02-03');
+---------------------------------------------------------+
|DAYOFWEEK('1998-02-03')                                  |
+---------------------------------------------------------+
| 3                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

DAYOFYEAR

DAYOFYEAR(date)

mysql> SELECT DAYOFYEAR('1998-02-03');
+---------------------------------------------------------+
| DAYOFYEAR('1998-02-03')                                 |
+---------------------------------------------------------+
| 34                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

EXTRACT

EXTRACT(unit FROM date)

mysql> SELECT EXTRACT(YEAR FROM '1999-07-02');
+---------------------------------------------------------+
| EXTRACT(YEAR FROM '1999-07-02')                         |
+---------------------------------------------------------+
| 1999                                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03');
+---------------------------------------------------------+
| EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03')          |
+---------------------------------------------------------+
| 199907                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

FROM_DAYS

FROM_DAYS(N)

mysql> SELECT FROM_DAYS(729669);
+---------------------------------------------------------+
| FROM_DAYS(729669)                                       |
+---------------------------------------------------------+
| 1997-10-07                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

FROM_UNIXTIME

FROM_UNIXTIME(unix_timestamp)

mysql> SELECT FROM_UNIXTIME(875996580);
+---------------------------------------------------------+
| FROM_UNIXTIME(875996580)                                |
+---------------------------------------------------------+
| 1997-10-04 22:23:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

HOUR

HOUR(time)

mysql> SELECT HOUR('10:05:03');
+---------------------------------------------------------+
| HOUR('10:05:03')                                        |
+---------------------------------------------------------+
| 10                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

LAST_DAY

LAST_DAY(date)

mysql> SELECT LAST_DAY('2003-02-05');
+---------------------------------------------------------+
| LAST_DAY('2003-02-05')                                  |
+---------------------------------------------------------+
| 2003-02-28                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

MAKEDATE

MAKEDATE(year,dayofyear)

mysql> SELECT MAKEDATE(2001,31), MAKEDATE(2001,32);
+---------------------------------------------------------+
| MAKEDATE(2001,31), MAKEDATE(2001,32)                    |
+---------------------------------------------------------+
| '2001-01-31', '2001-02-01'                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

MAKETIME

MAKETIME(hour,minute,second)

mysql> SELECT MAKETIME(12,15,30);
+---------------------------------------------------------+
| MAKETIME(12,15,30)                                      |
+---------------------------------------------------------+
| '12:15:30'                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

MICROSECOND

MICROSECOND(expr)

mysql> SELECT MICROSECOND('12:00:00.123456');
+---------------------------------------------------------+
| MICROSECOND('12:00:00.123456')                          |
+---------------------------------------------------------+
| 123456                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

MINUTE

MINUTE(time)

mysql> SELECT MINUTE('98-02-03 10:05:03');
+---------------------------------------------------------+
| MINUTE('98-02-03 10:05:03')                             |
+---------------------------------------------------------+
| 5                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

MONTH

MONTH(date)

mysql> SELECT MONTH('1998-02-03')
+---------------------------------------------------------+
| MONTH('1998-02-03')                                     |
+---------------------------------------------------------+
| 2                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

MONTHNAME

MONTHNAME(date)

mysql> SELECT MONTHNAME('1998-02-05');
+---------------------------------------------------------+
| MONTHNAME('1998-02-05')                                 |
+---------------------------------------------------------+
| February                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

NOW

NOW(), CURRENT_TIMESTAMP(), LOCALTIME(), LOCALTIMESTAMP()

mysql> SELECT NOW();
+---------------------------------------------------------+
| NOW()                                                   |
+---------------------------------------------------------+
| 1997-12-15 23:50:26                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

PERIOD_ADD

PERIOD_ADD(P,N)

mysql> SELECT PERIOD_ADD(9801,2);
+---------------------------------------------------------+
| PERIOD_ADD(9801,2)                                      |
+---------------------------------------------------------+
| 199803                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

PERIOD_DIFF

PERIOD_DIFF(P1,P2)

mysql> SELECT PERIOD_DIFF(9802,199703);
+---------------------------------------------------------+
| PERIOD_DIFF(9802,199703)                                |
+---------------------------------------------------------+
| 11                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

QUARTER

QUARTER(date)

mysql> SELECT QUARTER('98-04-01');
+---------------------------------------------------------+
| QUARTER('98-04-01')                                     |
+---------------------------------------------------------+
| 2                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

SECOND

SECOND(time)

mysql> SELECT SECOND('10:05:03');
+---------------------------------------------------------+
| SECOND('10:05:03')                                      |
+---------------------------------------------------------+
| 3                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

SEC_TO_TIME

SEC_TO_TIME(seconds)

mysql> SELECT SEC_TO_TIME(2378);
+---------------------------------------------------------+
| SEC_TO_TIME(2378)                                       |
+---------------------------------------------------------+
| 00:39:38                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

STR_TO_DATE

STR_TO_DATE(str,format)

mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
+---------------------------------------------------------+
| STR_TO_DATE('04/31/2004', '%m/%d/%Y')                   |
+---------------------------------------------------------+
| 2004-04-31                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

SUBDATE

SUBDATE(date,INTERVAL expr unit), SUBDATE(expr,days)

mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| DATE_SUB('1998-01-02', INTERVAL 31 DAY)                 |
+---------------------------------------------------------+
| 1997-12-02                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| SUBDATE('1998-01-02', INTERVAL 31 DAY)                  |
+---------------------------------------------------------+
| 1997-12-02                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

SUBTIME

SUBTIME(expr1,expr2)

mysql> SELECT SUBTIME('1997-12-31 23:59:59.999999',
   -> '1 1:1:1.000002');
+---------------------------------------------------------+
| SUBTIME('1997-12-31 23:59:59.999999'...                 |
+---------------------------------------------------------+
| 1997-12-30 22:58:58.999997                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

SYSDATE

SYSDATE()

mysql> SELECT SYSDATE();
+---------------------------------------------------------+
| SYSDATE()                                               |
+---------------------------------------------------------+
| 2006-04-12 13:47:44                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

TIME

TIME(expr)

mysql> SELECT TIME('2003-12-31 01:02:03');
+---------------------------------------------------------+
| TIME('2003-12-31 01:02:03')                             |
+---------------------------------------------------------+
| 01:02:03                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

TIMEDIFF

TIMEDIFF(expr1,expr2)

mysql> SELECT TIMEDIFF('1997-12-31 23:59:59.000001',
   -> '1997-12-30 01:01:01.000002');
+---------------------------------------------------------+
| TIMEDIFF('1997-12-31 23:59:59.000001'.....              |
+---------------------------------------------------------+
|  46:58:57.999999                                        |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

TIMESTAMP

TIMESTAMP(expr), TIMESTAMP(expr1,expr2)

mysql> SELECT TIMESTAMP('2003-12-31');
+---------------------------------------------------------+
| TIMESTAMP('2003-12-31')                                 |
+---------------------------------------------------------+
| 2003-12-31 00:00:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

TIMESTAMPADD

TIMESTAMPADD(unit,interval,datetime_expr)

mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');
+---------------------------------------------------------+
| TIMESTAMPADD(MINUTE,1,'2003-01-02')                     |
+---------------------------------------------------------+
| 2003-01-02 00:01:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

TIMESTAMPDIFF

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
+---------------------------------------------------------+
| TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01')          |
+---------------------------------------------------------+
| 3                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

TIME_FORMAT

TIME_FORMAT(time,format)

mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
+---------------------------------------------------------+
| TIME_FORMAT('100:00:00', '%H %k %h %I %l')              |
+---------------------------------------------------------+
| 100 100 04 04 4                                         |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

TIME_TO_SEC

TIME_TO_SEC(time)

mysql> SELECT TIME_TO_SEC('22:23:00');
+---------------------------------------------------------+
| TIME_TO_SEC('22:23:00')                                 |
+---------------------------------------------------------+
| 80580                                                   |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

TO_DAYS

TO_DAYS(date)

mysql> SELECT TO_DAYS(950501);
+---------------------------------------------------------+
| TO_DAYS(950501)                                         |
+---------------------------------------------------------+
| 728779                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

UNIX_TIMESTAMP

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

mysql> SELECT UNIX_TIMESTAMP();
+---------------------------------------------------------+
| UNIX_TIMESTAMP()                                        |
+---------------------------------------------------------+
| 882226357                                               |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
+---------------------------------------------------------+
| UNIX_TIMESTAMP('1997-10-04 22:23:00')                   |
+---------------------------------------------------------+
| 875996580                                               |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

UTC_DATE

UTC_DATE()

mysql> SELECT UTC_DATE(), UTC_DATE() + 0;
+---------------------------------------------------------+
| UTC_DATE(), UTC_DATE() + 0                              |
+---------------------------------------------------------+
| 2003-08-14, 20030814                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

UTC_TIME

UTC_TIME()

mysql> SELECT UTC_TIME(), UTC_TIME() + 0;
+---------------------------------------------------------+
| UTC_TIME(), UTC_TIME() + 0                              |
+---------------------------------------------------------+
| 18:07:53, 180753                                        |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

UTC_TIMESTAMP

UTC_TIMESTAMP()

mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;
+---------------------------------------------------------+
| UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0                    |
+---------------------------------------------------------+
| 2003-08-14 18:08:04, 20030814180804                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

WEEK

WEEK(date[,mode])

Mode First Day of week Range
0 Sunday 0-53
1 Monday 0-53
2 Sunday 1-53
3 Monday 1-53
4 Sunday 0-53
5 Monday 0-53
6 Sunday 1-53
7 Monday 1-53
mysql> SELECT WEEK('1998-02-20');
+---------------------------------------------------------+
| WEEK('1998-02-20')                                      |
+---------------------------------------------------------+
| 7                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

WEEKDAY

WEEKDAY(date)

mysql> SELECT WEEKDAY('1998-02-03 22:23:00');
+---------------------------------------------------------+
| WEEKDAY('1998-02-03 22:23:00')                          |
+---------------------------------------------------------+
| 1                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

WEEKOFYEAR

WEEKOFYEAR(date)

mysql> SELECT WEEKOFYEAR('1998-02-20');
+---------------------------------------------------------+
| WEEKOFYEAR('1998-02-20')                                |
+---------------------------------------------------------+
| 8                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

YEAR

YEAR(date)

mysql> SELECT YEAR('98-02-03');
+---------------------------------------------------------+
| YEAR('98-02-03')                                        |
+---------------------------------------------------------+
| 1998                                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

YEARWEEK

YEARWEEK(date), YEARWEEK(date,mode)

mysql> SELECT YEARWEEK('1987-01-01');
+---------------------------------------------------------+
| YEAR('98-02-03')YEARWEEK('1987-01-01')                  |
+---------------------------------------------------------+
| 198653                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

 

 

Reference:

รวมคำสั่ง SQL Command พื้นฐานเบื้องต้น

Database, Oracle, MySQL

 

 

Author: Suphakit Annoppornchai

Credit: https://saixiii.com, https://www.tutorialspoint.com

Leave a Reply