PHP and MySQL syntax: Difference between revisions
m (→timer) |
m (→timer) |
||
Line 161: | Line 161: | ||
SELECT @timer := CURRENT_TIMESTAMP(); | SELECT @timer := CURRENT_TIMESTAMP(); | ||
SELECT SLEEP(2); //sleep 2 seconds for testing purpose | SELECT SLEEP(2); //sleep 2 seconds for testing purpose | ||
SELECT 'custom message' AS 'action', CURRENT_TIMESTAMP() AS 'end time', @timer AS 'start time', TIMEDIFF(CURRENT_TIMESTAMP(), @timer) AS ' | SELECT 'custom message' AS 'action', CURRENT_TIMESTAMP() AS 'end time', @timer AS 'start time', TIMEDIFF(CURRENT_TIMESTAMP(), @timer) AS 'time elapsed'; | ||
</pre> | </pre> | ||
Revision as of 15:02, 13 May 2016
mulitple approaches to completing the same/similar task using PHP, MySQL and Excel
time / timestamp
terms of time format
format \ date | 1900/1/1 | 1970/1/1 | 2016/1/1 |
column value of unix timestamp (the number of seconds since 1970/01/01) |
-2209075200 | 0 | 1451606400 |
column value of Excel DATEVALUE function (number of days since 1900/1/1) |
1 | 25569 | 42370 |
now
human readable time format
Convert unix timestamp (the number of seconds since 1970/01/01) <--> to the human readable time format ex: 2024-04-28 21:18:06
- php: echo date("Y-m-d H:i:s", time() ); //Convert the time stamp of current time to the human readable time format. Ex: return '2024-04-28 21:18:06' server timezone dependent
- mysql: default client timezone is server timezone[1]
- SELECT NOW(); Returns 2024-04-28 21:18:06 which mapping to DATETIME type[2]
- SELECT NOW()+0; //2011-04-01 12:19:43 returns 20110401122023.000000
- SELECT CURRENT_TIMESTAMP; Returns 2024-04-28 21:18:06 Current time zone of MySQL is SYSTEM & client timezone dependent.
- convert mysql datatype timestamp (UTC) to Taipei datetime (client timezone dependent) [3]
/* approach 1: */ SET time_zone='+8:00'; SELECT CURRENT_TIMESTAMP(); /* approach 2: Detect server timezone automatically! Note the precision of time difference between server timezone to your preferred timezone is hours NOT to minutes */ SELECT convert_tz( CURRENT_TIMESTAMP(), CONCAT( ROUND( TIME_TO_SEC(timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')))/3600), ':00') , '+08:00'); /* approach 3: If you already know the server timezone ex: -05:00 */ SELECT convert_tz(CURRENT_TIMESTAMP, '-05:00', '+08:00');
- shell script with GUN date installed in Linux , Mac or Cygwin of Win :
- echo `date +\%Y-\%m-\%d\ %H:%M:%S` 2>&1 or date +\%Y-\%m-\%d\ %H:%M:%S Returns 2024-04-28 21:18:06 server timezone dependent
- echo `TZ=Asia/Taipei date +\%Y-\%m-\%d\ %H:%M:%S` 2>&1 or TZ=Asia/Taipei date +\%Y-\%m-\%d\ %H:%M:%S Returns 2024-04-28 21:18:06 force to use Taipei timezone
current timestamp
the number of seconds since 1970/01/01. UTC±0
- php: echo time();
- php: echo microtime(); PHP: microtime - Manual "Returns current Unix timestamp with microseconds" //ex: 0.45920500 1406776901
- mysql: SELECT UNIX_TIMESTAMP(); //ex: 2011-05-30 01:56:38 returns 1306720622
- javascript: getTime() Method ex: new Date().getTime(); //Return the number of milliseconds since 1970/01/01 [4]
- EXCEL: Excel date to Unix timestamp - Stack Overflow
compare the timestamp and human readable time format using MySQL
SELECT @@global.time_zone, @@session.time_zone; SELECT UNIX_TIMESTAMP( ) , FROM_UNIXTIME( UNIX_TIMESTAMP( ) , '%Y-%m-%d %H:%i:%S' ); /* 1st column: timezone of UNIX_TIMESTAMP( ) is UTC 2nd column: 'FROM_UNIXTIME( UNIX_TIMESTAMP( ) , '%Y-%m-%d %H:%i:%S' )' is server timezone dependent */
today & yesterday
today
- MySQL: SELECT CURRENT_DATE; or SELECT CURDATE(); Returns 2024-04-28 [5]
- EXCEL: =TODAY() Returns 2024/04/28 [6]
- shell script in Linux or Mac with GUN date installed: echo `date +\%Y-\%m-\%d` 2>&1 or date +"%Y-%m-%d" Returns 2024-04-28[7]
yesterday
- MySQL:
- EXCEL:
- PHP:
- shell script in Linux with GUN date installed: echo $(date -d "yesterday" '+%Y-%m-%d')[8] //Returns sample: 2024-04-24
specified time
convert the unix timestamp to human readable time format
ex: 2010-12-21 10:05:06
- PHP: echo date("Y-m-d H:i:s", 1292897201 ); // Using date() function that converting the time stamp 1292897201 to the human readable time format. similar: gmdate() function [Last visited: 2014-04-30] server timezone dependent
- MySQL:
- SELECT FROM_UNIXTIME( 1306311155 ); //convert the time stamp 1306311155 to the human readable time format 2011-05-25 08:12:11 server timezone dependent
- SELECT FROM_UNIXTIME( 1306311155, '%Y-%m-%d %H:%i:%S' ); //convert the time stamp 1306311155 to the human readable time format 2011-05-25 08:12:11 server timezone dependent
- convert mysql datatype timestamp (UTC) to Taipei datetime (client timezone dependent) [9]
/* approach1 */ SET time_zone='+8:00'; SELECT FROM_UNIXTIME( `column_of_timestamp` , '%Y-%m-%d %H:%i:%S' ) FROM `table`; /* approach2: detect server timezone automatically. Note the precision of time difference between server timezone to your preferred timezone is hours NOT to minutes */ SELECT convert_tz( FROM_UNIXTIME( `column_of_timestamp` , '%Y-%m-%d %H:%i:%S' ), CONCAT( ROUND( TIME_TO_SEC(timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')))/3600), ':00') , '+08:00') FROM `table` /* approach3: If you already know the server timezone ex: -05:00 */ SELECT convert_tz( FROM_UNIXTIME( `column_of_timestamp` , '%Y-%m-%d %H:%i:%S' ), '-05:00', '+08:00') FROM `table`
- Excel
- =( unix_time_stamp /86400)+DATE(1970,1,1) // convert the unix time stamp 1421539200 to the human readable time format 2015/1/18 [10]
convert human-readable time to timestamp
- PHP: using strtotime() function ex: strtotime('2010-12-21 10:05:06') [11]
- mysql:
- SELECT UNIX_TIMESTAMP('2011-03-15 18:53:57'); /* return timestamp: 1300186437 */ server timezone dependent
- SELECT UNIX_TIMESTAMP(STR_TO_DATE('2011-03-15 18:53:57', '%Y-%m-%d %H:%i:%S')); /* return timestamp: 1300186437 */ server timezone dependent
- Excel:
- input data (A2 cell): YYYY/MM/DD (e.g. 2024/04/28 ) or YYYY/MM/DD HH:MM (e.g. 2024/04/28 21:18)
- =(A2-DATE(1970,1,1))*86400 [12]
convert datevalue (number of days since 1900/1/1) ↔ to human-readable date
- Excel: =DATEVALUE(Cell_of_date) // ex: =DATEVALUE("2015/1/27") returns 42031. DATEVALUE of 1900/1/1 is 1
- Excel: =TEXT(Cell_of_datevalue,"yyyy/mm/dd") // ex: =TEXT(42031,"yyyy/mm/dd") returns 2015/1/27
convert month name to numeric date ex: April 28, 2024 -> 2024-04-28
- PHP:
- MySQL: STR_TO_DATE(str,format) ex: SELECT STR_TO_DATE('April 28, 2024','%M %d, %Y');[13]
- Excel
PHP microtime to MySQL timestamp
PHP microtime to MySQL timestamp
time difference of two time values
- php: using mktime() function
- mysql: using TIMEDIFF() function ex: SELECT TIMEDIFF('2016:02:01 00:00:00', '2016:01:01 00:00:00'); /* return 744:00:00 */
date interval
When people said 2024-04-01 ~ 2024-04-03, it means 2024-04-01 00:00:00 ~ 2024-04-03 59:59:59 in the system.
Query by month
mysql ex: find the records in January, 2024[14] Data type of column `time` is datetime (ex: yy:mm:dd hh:mm:ss). Using one of sql queries as following. online demo
- SELECT * FROM `table_name` WHERE YEAR(`time`) = '2024' AND MONTH(`time`) = '1' /*MONTH(`time`) = '1' or MONTH(`time`) = '01' are both ok*/
- SELECT * FROM `table_name` WHERE DATE_FORMAT(`time`, '%Y-%m') = '2024-01'
- SELECT * FROM `table_name` WHERE `time` LIKE '2024-01%' /*find the time records starting with the string 2024-01 */
- SELECT * FROM `table_name` WHERE DATE(`time`) between '2024-01-01' AND '2024-01-31' add the DATE function to the `time` field
- SELECT * FROM `table_name` WHERE `time` >= '2024-01-01 00:00:00' AND `time` < '2024-02-01 00:00:00'
- SELECT * FROM `table_name` WHERE `time` >= '2024-01-01 00:00:00' AND `time` <= '2024-01-31 59:59:59'
date format
sample output: 2024-04-28
- Use MySQL DATE() Function: DATE( time )
- Use MySQL DATE_FORMAT() Function: DATE_FORMAT( time, '%Y-%m-%d')
timer
SELECT @timer := CURRENT_TIMESTAMP(); SELECT SLEEP(2); //sleep 2 seconds for testing purpose SELECT 'custom message' AS 'action', CURRENT_TIMESTAMP() AS 'end time', @timer AS 'start time', TIMEDIFF(CURRENT_TIMESTAMP(), @timer) AS 'time elapsed';
further reading
- PHP Date and Time functions
- MySQL The DATETIME, DATE, and TIMESTAMP Types
- MySQL Date and Time Functions
- PHP Date / Time Functions / PHP Date / Time 函数
- Help:Magic words - MediaWiki
Math
Format a number
- PHP: PHP: number_format - Manual
- MySQL: LPAD(number,3,0) will output 007 if the value of number is 7
Round a number downward to its nearest integer
other
- a >0 OR b > 0 OR c > 0
- a+b+c > 0
trigger to make backup of deleted data before deleting them
MySQL :: Re: before delete trigger to make backup of deleted data
step 1: create table hello_deleted_data with the same structure of table hello
step 2: add a trigger
create trigger hello before delete on hello for each row insert into hello_deleted_data (x,y,z) values (OLD.x, OLD.y, OLD.z);
tested on version: mysqlnd 5.0.7-dev - 091210
redirect/reload to (another) page
redirect
- HTML: html redirect
- PHP:
- header() to new location PHP: header - Manual
- echo HTML redirect
- Javascript
- Javascript Tutorial - Redirect ex: window.location = "http://www.google.com/"
- iframe - Changing parent window's URL from IFRAME content - AV5
reload
- Javascript
- iframe (reload) - parent.location.reload();
find unique (non duplicated) data
- SELECT DISTINCT column FROM table[15]
- (1)SELECT * FROM table; (2)Using PHP: array_unique for data handling two or more columns
- SELECT column FROM table GROUP BY column
text match
- PHP in_array; jQuery.inArray()
//PHP code snippet: $array = array("AAA", "BBB", "CCC"); if(in_array($var, $array){ //do something }
- preg_match...
- verbose approach: IF (0 == 'String') always be true?[16]
if($var =="AAA" OR $var =="BBB" OR $var =="CCC"){ //do something }
text input filter
export/import sql file
reverse the boolean value
- PHP:
$var = true; print( !$var); //add ! symbol to reverse the boolean value
- EXCEL: NOT 函數 - Excel ex: =NOT(TRUE) will return FALSE
ideas
filter some records
- approach 1: (1) select * under some condition, (2) collect the $identifiers which matched the condition. and then (3) select some records NOT IN ( $identifiers )
- approach 2: (1) select * and then (2) unset some records which matched the condition.
documentation
How to generate the table schema
- Using phpMyAdmin or other MySQL client
- Input the SQL query:
EXPLAIN table_name;
- Print view (with full texts)
- Easy to copy the table to other work processor
- (optional) Adjust the text size to 10
debug
references
- ↑ MySQL :: MySQL 5.5 Reference Manual :: 10.6 MySQL Server Time Zone Support
- ↑ MySQL :: MySQL 5.1 Reference Manual :: 11.3.1 The DATE, DATETIME, and TIMESTAMP Types
- ↑ timezone - How do I get the current time zone of MySQL? - Stack Overflow
- ↑ JavaScript getTime() Method
- ↑ MySQL :: MySQL 5.5 Reference Manual :: 12.7 Date and Time Functions
- ↑ TODAY function - Excel - Office.com
- ↑ Using the ‘date’ command in your crontab
- ↑ Get yesterday's date in bash on Linux, DST-safe - Stack Overflow & unix - Get the date (a day before current time) in Bash - Stack Overflow
- ↑ timezone - How do I get the current time zone of MySQL? - Stack Overflow
- ↑ Excel Timestamp to Date ← Automate Everything
- ↑ How to convert date to timestamp in PHP? - Stack Overflow
- ↑ Excel date to Unix timestamp - Stack Overflow
- ↑ MySQL MONTHNAME() from numbers - Stack Overflow
- ↑ sql - MySQL Query GROUP BY day / month / year - Stack Overflow
- ↑ MySQL :: MySQL 5.0 Reference Manual :: 8.3.1.13 DISTINCT Optimization
- ↑ Why does PHP consider 0 to be equal to a string? - Stack Overflow