Wednesday, April 5, 2017

How does one get the time difference between two date columns SQL

How does one get the time difference between two date columns
 in hours , minutes and seconds



SELECT floor((D1-D2)*24)
      || ' HOURS ' ||
          mod(floor((D1-D2)*24*60),60)
        || ' MINUTES ' ||
        mod(floor((D1-D2)*24*60*60),60)
         || ' SECS ' time_difference
    FROM honnikery;



TIME_DIFF
--------------------------------------------------------------------------------
1 HOURS 0 MINUTES 0 SECS
1 HOURS 10 MINUTES 0 SECS
10 HOURS 1 MINUTES 10 SECS

Tuesday, February 28, 2017

Finding tables without Primary or Unique Keys (Mysql)

Finding tables without Primary or Unique Keys:


SELECT t.TABLE_SCHEMA,t.TABLE_NAME,ENGINE
FROM information_schema.TABLES t
INNER JOIN information_schema.COLUMNS c
ON t.TABLE_SCHEMA=c.TABLE_SCHEMA
AND t.TABLE_NAME=c.TABLE_NAME
AND t.TABLE_SCHEMA NOT IN ('performance_schema','information_schema','mysql')
GROUP BY t.TABLE_SCHEMA,t.TABLE_NAME
HAVING sum(if(column_key in ('PRI','UNI'), 1,0))=0;





Finding Foreign key constraints:


SELECT referenced_table_name parent, table_name child, constraint_name
FROM information_schema.KEY_COLUMN_USAGE
WHERE referenced_table_name IS NOT NULL
ORDER BY referenced_table_name;

Wednesday, January 18, 2017

MySQL Error Handling


Three type of Handler_Action:

CONTINUE
EXIT
UNDO

Type of Condition Value:

mysql_error_code
sqlstate_value
SQLWarning
SQLException
NotFound



GET DIAGNOSTICS, you can get all of the error information, and you should, if not already.

If you were going to use GET DIAGNOSTICS from the command line, you could use something like this (immediately following your query):

GET DIAGNOSTICS CONDITION 1
 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SELECT @sqlstate, @errno, @text;

MySQL error code: 1175 during UPDATE in MySQL Workbench

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect. 0.000 sec


Try disabling that using these steps -
Edit > Preferences > Sql Editor > uncheck the "Safe Updates"
Note - try reconnecting the server (Query > Reconnect to Server) and than run your query again.

Monday, July 4, 2016

MySQL date and time functions


MySQL date and time functions
ADDDATE()
ADDTIME()
CONVERT_TZ()
CURDATE()
CURRENT_DATE()
CURRENT_TIME()
CURRENT_ TIMESTAMP()
CURTIME()
DATE_ADD()
DATE_FORMAT()
DATE_SUB()
DATE()
DATEDIFF()
DAY()
DAYNAME()
DAY OF MONTH()
DAY OF WEEK()
DAY OF YEAR()
EXTRACT()
FROM_DAYS()
FROM_UNIXTIME()
GET_FORMAT()
HOUR()
LAST_DAY()
LOCALTIME()
LOCALTIMESTAMP()
MAKEDATE()
MAKETIME()
MICROSECOND()
MINUTE()
MONTH()
MONTHNAME()
NOW()
PERIOD_ADD()
PERIOD_DIFF()
QUARTER()
SEC_TO_TIME()
SECOND()
STR_TO_DATE()
SUBDATE()
SUBTIME()
SYSDATE()
TIME_FORMAT()
TIME_TO_SEC()
TIME()
TIMEDIFF()
TIMESTAMP()
TIMESTAMPADD()
TIMESTAMPDIFF()
TO_DAYS()
UNIX_TIMESTAMP()
UTC_DATE()
UTC_TIME()
UTC_TIMESTAMP()
WEEK()
WEEKDAY()
WEEK OF YEAR()
YEAR()
YEARWEEK()

Thursday, April 28, 2016

MySQL Functions



1)  The MySQL LAST_INSERT_ID function to get the last insert id that MySQL has been generated.
2)  STR_TO_DATE() function to convert strings into a date and/or time values.

   SELECT STR_TO_DATE('20130101 113012','%Y%m%d %h%i%s') ;

      2013-01-01 11:30:12