MySQL Programming Questions And Answers

81⟩ How To Write Date and Time Literals?

MySQL offers a number of formats for you to use to enter date and time literals:

► ANSI standard format: "YYYY-MM-DD HH:MM:SS".

► Non-standard limiters. Like: "YYYY/MM/DD HH^MM^SS" or "YYYY.MM.DD HH-MM-SS".

► No limiters. Like: "YYYYMMDD" for a date or "HHMMSS" for a time.

► Decimal numbers. Like: 8 digits dddddddd for a date or 6 digits dddddd for a time.

The tutorial exercise below gives you some good examples:

SELECT DATE('1997-01-31') FROM DUAL;

1997-01-31

SELECT DATE('1997-01-31 09:26:50') FROM DUAL;

1997-01-31

SELECT TIME('1997-01-31 09:26:50') FROM DUAL;

09:26:50

SELECT DATE('1997/01/31 09^26^50') FROM DUAL;

1997-01-31

SELECT TIME('1997/01/31 09^26^50') FROM DUAL;

09:26:50

SELECT DATE('19970131') FROM DUAL;

1997-01-31

SELECT TIME('092650') FROM DUAL;

09:26:50

SELECT DATE(19970131) FROM DUAL; -- Crazy format

1997-01-31

SELECT TIME(092650) FROM DUAL; -- Crazy format

09:26:50

Is this answer helpful? 0 Yes | 0 No

Answer This Question

169 views

82⟩ How To Increment Dates by 1 in MySQL?

If you have a date, and you want to increment it by 1 day, you can use the DATE_ADD(date, INTERVAL 1 DAY) function. You can also use the date interval add operation as "date + INTERVAL 1 DAY". The tutorial exercise below gives you some good examples:

SELECT DATE_ADD(DATE('1997-01-31'), INTERVAL 1 DAY)

FROM DUAL;

1997-02-01

SELECT DATE('1997-01-31') + INTERVAL 1 DAY FROM DUAL;

1997-02-01

Is this answer helpful? 0 Yes | 0 No

Answer This Question

178 views

83⟩ What Are Date and Time Intervals?

A date and time interval is special value to be used to increment or decrement a date or a time at a given date or time unit. A data and time interval should be expression in the format of "INTERVAL expression unit", where "unit" and "expression" should follow these rules:

unit expression value format

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'

Is this answer helpful? 0 Yes | 0 No

Answer This Question

179 views

84⟩ How To Decrement Dates by 1 in MySQL?

If you have a date, and you want to decrement it by 1 day, you can use the DATE_SUB(date, INTERVAL 1 DAY) function. You can also use the date interval subtraction operation as "date - INTERVAL 1 DAY". The tutorial exercise below gives you some good examples:

SELECT DATE_SUB(DATE('1997-03-01'), INTERVAL 1 DAY)

FROM DUAL;

1997-02-28

SELECT DATE('1997-01-31') - INTERVAL 1 DAY FROM DUAL;

1997-02-28

Is this answer helpful? 0 Yes | 0 No

Answer This Question

180 views

85⟩ How To Convert Character Strings to Dates?

If you have a character string that represents a date, and you want to convert it into a date value, you can use the STR_TO_DATE(string, format) function. STR_TO_DATE() shares the same formatting codes with DATE_FORMAT() function. The tutorial exercise below shows you some good examples:

SELECT STR_TO_DATE('Friday, January 31, 1997',

'%W, %M %e, %Y') FROM DUAL;

1997-01-31

SELECT STR_TO_DATE('Friday, January 31, 1997, 09:26:50 AM',

'%W, %M %e, %Y, %h:%i:%s %p') FROM DUAL;

1997-01-31 09:26:50

SELECT STR_TO_DATE('31-Jan-1997 09:26:50.000123',

'%d-%b-%Y %H:%i:%s.%f') FROM DUAL;

1997-01-31 09:26:50.000123

Is this answer helpful? 0 Yes | 0 No

Answer This Question

197 views

86⟩ How To Convert Dates to Character Strings?

You can convert dates to character strings using the DATE_FORMAT(date, format) function. MySQL supports the following basic formatting codes:

► %a Abbreviated weekday name (Sun..Sat)

► %b Abbreviated month name (Jan..Dec)

► %c Month, numeric (0..12)

► %D Day of the month with English suffix (0th, 1st, 2nd, 3rd,...)

► %d Day of the month, numeric (00..31)

► %e Day of the month, numeric (0..31)

► %f Microseconds (000000..999999)

► %H Hour (00..23)

► %h Hour (01..12)

► %I Hour (01..12)

► %i Minutes, numeric (00..59)

► %j Day of year (001..366)

► %k Hour (0..23)

► %l Hour (1..12)

► %M Month name (January..December)

► %m Month, numeric (00..12)

► %p AM or PM

► %r Time, 12-hour (hh:mm:ss followed by AM or PM)

► %S Seconds (00..59)

► %s Seconds (00..59)

► %T Time, 24-hour (hh:mm:ss)

► %W Weekday name (Sunday..Saturday)

► %w Day of the week (0=Sunday..6=Saturday)

► %Y Year, numeric, four digits

► %y Year, numeric (two digits)

Is this answer helpful? 0 Yes | 0 No

Answer This Question

185 views

87⟩ How To Present a Past Time in Hours, Minutes and Seconds?

If you want show an article was posted "n hours n minutes and n seconds ago", you can use the TIMEDIFF(NOW(), pastTime) function as shown in the following tutorial exercise:

SELECT TIMEDIFF(NOW(), '2006-07-01 04:09:49') FROM DUAL;

06:42:58

SELECT TIME_FORMAT(TIMEDIFF(NOW(), '2006-06-30 04:09:49'),

'%H hours, %i minutes and %s seconds ago.') FROM DUAL;

30 hours, 45 minutes and 22 seconds ago.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

167 views

88⟩ How To Calculate the Difference between Two Time Values?

If you have two time values, and you want to know the time difference between them, you can use the TIMEDIFF(time1, time2) function as shown below:

SELECT TIMEDIFF(TIME('19:26:50'), TIME('09:26:50'))

FROM DUAL;

10:00:00

SELECT TIMEDIFF('1997-03-01 19:26:50.000123',

'1997-02-28 09:26:50.000000') FROM DUAL;

34:00:00.000123

Is this answer helpful? 0 Yes | 0 No

Answer This Question

197 views

89⟩ How To Extract a Unit Value from a Date and Time?

If you want to extract a specific date or time unit value out of a date or a time, you can use the EXTRACT(unit FROM expression) function. The tutorial exercise below gives you some good examples:

ELECT EXTRACT(DAY FROM NOW()) FROM DUAL;

28

ELECT EXTRACT(HOUR FROM NOW()) FROM DUAL;

23

ELECT EXTRACT(SECOND FROM NOW()) FROM DUAL;

36

Is this answer helpful? 0 Yes | 0 No

Answer This Question

193 views

90⟩ How To Create a New Table in MySQL?

If you want to create a new table, you can use the "CREATE TABLE" statement. The following tutorial script shows you how to create a table called "tip":

mysql> CREATE TABLE tip (id INTEGER PRIMARY KEY,

subject VARCHAR(80) NOT NULL,

description VARCHAR(256) NOT NULL,

create_date DATE NULL);

Query OK, 0 rows affected (0.08 sec)

This scripts creates a testing table called "tip" with 4 columns in the current database.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

198 views

91⟩ What Are DDL Statements in MySQL?

DDL (Data Definition Language) statements are statements to create and manage data objects in the database. The are 3 primary DDL statements:

► CREATE - Creating a new database object.

► ALTER - Altering the definition of an existing data object.

► DROP - Dropping an existing data object.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

189 views

92⟩ How Many Ways to Get the Current Time?

There are 8 ways to get the current time:

SELECT NOW() FROM DUAL;

2006-07-01 10:02:41

SELECT CURRENT_TIME() FROM DUAL;

10:02:58

SELECT SYSDATE() FROM DUAL;

2006-07-01 10:03:21

mysql> SELECT CURRENT_TIMESTAMP() FROM DUAL;

2006-07-01 10:04:03

SELECT LOCALTIME() FROM DUAL;

2006-07-01 10:07:37

mysql> SELECT LOCALTIMESTAMP() FROM DUAL;

2006-07-01 10:08:08

mysql> SELECT UTC_TIME() FROM DUAL;

14:09:22

mysql> SELECT UTC_TIMESTAMP() FROM DUAL;

2006-07-01 14:09:49

Is this answer helpful? 0 Yes | 0 No

Answer This Question

191 views

93⟩ What Is TIMESTAMP in MySQL?

A TIMESTAMP data type allows you to record a date and time like DATETIME data type. But it has some interesting features when used on a table column:

► The first TIMESTAMP column in a table will be assigned with the current date and time, if it is not listed in an INSERT statement.

► The first TIMESTAMP column in a table will be assigned with the current date and time, if it is not listed in an UPDATE statement.

► If a TIMESTAMP column is assigned with NULL, the current date and time will be persisted to this column.

► You can turn off the default current date and time on INSERT by defining the column with "DEFAULT 0".

► The tutorial exercise below provides you 2 equivalent ways to create a TIMESTAMP column:

CREATE TABLE links (lastDate TIMESTAMP);

CREATE TABLE links (lastDate TIMESTAMP

DEFAULT CURRENT_TIMESTAMP

ON UPDATE CURRENT_TIMESTAMP);

Is this answer helpful? 0 Yes | 0 No

Answer This Question

187 views

94⟩ What Are Date and Time Functions in MySQL?

MySQL offers a number of functions for date and time values:

► ADDDATE(date, INTERVAL expr unit) - Adding days to a date. Same as DATE_ADD().

► ADDTIME(time1, time2) - Adding two time values together.

► CURDATE() - Returning the current date. Same as CURRENT_DATE().

► CURTIME() - Returning the current time. Same as CURRENT_TIME().

► DATE(expression) - Returning the date from the expression.

► DATEDIFF(date1, date2) - Returning dates difference in days.

► DATE_ADD(date, INTERVAL expr unit) - Adding days to a date.

► DATE_SUB(date, INTERVAL expr unit) - Subtracting days from a date.

► DATE_FORMAT(date, format) - Returning a character string representing a date.

► DAY(date) - Returning an integer representing the day of the month. Same as DAYOFMONTH()

► DAYNAME(date) - Returning the name of week day.

► DAYOFMONTH(date) - Returning an integer representing the day of the month.

► DAYOFWEEK(date) - Returning an integer representing the day of the week.

► DAYOFYEAR(date) - Returning an integer representing the day of the year.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

203 views

95⟩ How To Get a List of Columns in an Existing Table?

If you have an existing table, but you don't remember what are the columns used in the table, you can use the "SHOW COLUMNS FROM tableName" command to get a list of all columns of the specified table. You can also use the "DESCRIBE tableName" command, which gives you the same output as "SHOW COLUMNS" command. The following tutorial script shows you a good example:

mysql> SHOW COLUMNS FROM tip;

+-------------+--------------+------+-----+---------+-------

| Field  | Type  | Null | Key | Default | Extra 

+-------------+--------------+------+-----+---------+-------

| id  | int(11) | NO | PRI |  |  

| subject  | varchar(80) | NO |  |  |  

| description | varchar(256) | NO |  |  |  

| create_date | date  | YES |  | NULL |  

+-------------+--------------+------+-----+---------+-------

4 rows in set (0.04 sec)

Is this answer helpful? 0 Yes | 0 No

Answer This Question

188 views

96⟩ How To Get a List of All Tables in a Database?

If you want to see the table you have just created, you can use the "SHOW TABLES" command to get a list of all tables in database. The tutorial script gives you a good example:

mysql> SHOW TABLES;

+---------------+

| Tables_in_ggl |

+---------------+

| links  |

| tip   |

+---------------+

3 rows in set (0.00 sec)

Is this answer helpful? 0 Yes | 0 No

Answer This Question

201 views

97⟩ What Happens If You No CREATE Privilege in a Database?

In order to create tables in a database, your user account must have the CREATE privilege for that database. Otherwise you will get an error as shown in the following tutorial exercise:

>cd mysqlin

>mysql -u guest -ppub

mysql> use ggl;

Database changed

mysql> CREATE TABLE test (id integer);

ERROR 1142 (42000): CREATE command denied to user

'guest'@'localhost' for table 'test'

If you get this error, you need to see the DBA to obtain the CREATE privilege.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

184 views

98⟩ How To See the CREATE TABLE Statement of an Existing Table?

If you want to know how an existing table was created, you can use the "SHOW CREATE TABLE" command to get a copy of the "CREATE TABLE" statement back on an existing table. The following tutorial script shows you a good example:

mysql> SHOW CREATE TABLE tip;

+-------+-------------------------------

| Table | Create Table

+-------+-------------------------------

| tip | CREATE TABLE `tip` (

 `id` int(11) NOT NULL,

 `subject` varchar(80) NOT NULL,

 `description` varchar(256) NOT NULL,

 `create_date` date default NULL,

 PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

+-------+-------------------------------

1 row in set (0.38 sec)

Comparing with the original "CREATE TABLE" statement used in the previous tutorial, the output tells you that:

► INTEGER data type was replaced by "int(11)".

► Default database engine "MyISAM" was used for the table.

► Default character set "latin1" was used for the table.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

193 views

99⟩ How To Create a New Table by Selecting Rows from Another Table in MySQL ?

Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the "CREATE TABLE ... SELECT" statement. The tutorial script below gives you a good example:

mysql> INSERT INTO tip VALUES (1, 'Learn MySQL',

'Visit www.GlobalGuideLine.com','2006-07-01');

Query OK, 1 row affected (0.62 sec)

mysql> CREATE TABLE tipBackup SELECT * FROM tip;

Query OK, 1 row affected (0.49 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM tipBackup;

+----+-------------+-------------------------+-------------+

| id | subject  | description   | create_date |

+----+-------------+-------------------------+-------------+

| 1 | Learn MySQL | Visit www.GlobalGuideLine.com | 2006-07-01 |

+----+-------------+-------------------------+-------------+

1 row in set (0.00 sec)

As you can see, this SQL script created a table called "tipBackup" using the same column definitions as the "tip" table and copied all data rows into "tipBackup".

Is this answer helpful? 0 Yes | 0 No

Answer This Question

189 views

100⟩ How To Drop an Existing Table in MySQL?

If you want to delete an existing table and its data rows, you can use the "DROP TABLE" statement as shown in the tutorial script below:

mysql> SELECT * FROM tipBackup;

+----+-------------+-------------------------+-------------+

| id | subject  | description   | create_date |

+----+-------------+-------------------------+-------------+

| 1 | Learn MySQL | Visit www.GlobalGuideLine.com | 2006-07-01 |

+----+-------------+-------------------------+-------------+

1 row in set (0.40 sec)

mysql> DROP TABLE tipBackup;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM tipBackup;

ERROR 1146 (42S02): Table 'ggl.tipbackup' doesn't exist

Be careful, when you use the "DROP TABLE" statement. All data rows are gone too.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

176 views

MySQL Programming

Home Databases Programming MySQL Programming

110 MySQL Programming Questions And Answers

81⟩ How To Write Date and Time Literals?

82⟩ How To Increment Dates by 1 in MySQL?

83⟩ What Are Date and Time Intervals?

84⟩ How To Decrement Dates by 1 in MySQL?

85⟩ How To Convert Character Strings to Dates?

86⟩ How To Convert Dates to Character Strings?

87⟩ How To Present a Past Time in Hours, Minutes and Seconds?

88⟩ How To Calculate the Difference between Two Time Values?

89⟩ How To Extract a Unit Value from a Date and Time?

90⟩ How To Create a New Table in MySQL?

91⟩ What Are DDL Statements in MySQL?

92⟩ How Many Ways to Get the Current Time?

93⟩ What Is TIMESTAMP in MySQL?

94⟩ What Are Date and Time Functions in MySQL?

95⟩ How To Get a List of Columns in an Existing Table?

96⟩ How To Get a List of All Tables in a Database?

97⟩ What Happens If You No CREATE Privilege in a Database?

98⟩ How To See the CREATE TABLE Statement of an Existing Table?

99⟩ How To Create a New Table by Selecting Rows from Another Table in MySQL ?

100⟩ How To Drop an Existing Table in MySQL?

Quick Links:

MySQL Programming

Home Databases Programming MySQL Programming

110 MySQL Programming Questions And Answers

81⟩ How To Write Date and Time Literals?

82⟩ How To Increment Dates by 1 in MySQL?

83⟩ What Are Date and Time Intervals?

84⟩ How To Decrement Dates by 1 in MySQL?

85⟩ How To Convert Character Strings to Dates?

86⟩ How To Convert Dates to Character Strings?

87⟩ How To Present a Past Time in Hours, Minutes and Seconds?

88⟩ How To Calculate the Difference between Two Time Values?

89⟩ How To Extract a Unit Value from a Date and Time?

90⟩ How To Create a New Table in MySQL?

91⟩ What Are DDL Statements in MySQL?

92⟩ How Many Ways to Get the Current Time?

93⟩ What Is TIMESTAMP in MySQL?

94⟩ What Are Date and Time Functions in MySQL?

95⟩ How To Get a List of Columns in an Existing Table?

96⟩ How To Get a List of All Tables in a Database?

97⟩ What Happens If You No CREATE Privilege in a Database?

98⟩ How To See the CREATE TABLE Statement of an Existing Table?

99⟩ How To Create a New Table by Selecting Rows from Another Table in MySQL ?

100⟩ How To Drop an Existing Table in MySQL?

BE THE FIRST TO KNOW

Quick Links: