PHP and MySQL syntax: Difference between revisions

From LemonWiki共筆
Jump to navigation Jump to search
mNo edit summary
(3 intermediate revisions by the same user not shown)
Line 2: Line 2:


== time / timestamp ==
== time / timestamp ==
=== terms of time format ===
<div style="width:100%; min-height: .01%; overflow-x: auto;">
<table border="1" class="wikitable sortable nowrap">
<tr>
  <th> format \ date </th>
  <th> 1900/1/1</th>
  <th> 1970/1/1</th>
  <th> 2016/1/1 </th>
  <th> notes </th>
</tr>
<tr>
  <td> value of unix timestamp <br /> (the number of seconds since 1970/01/01<ref>[https://www.extendoffice.com/documents/excel/2473-excel-timestamp-to-date.html How to convert between date and Unix timestamp in Excel?]</ref>)</td>
  <td> -2209075200</td>
  <td> 0</td>
  <td> 1451606400</td>
  <td> </td>
</tr>
<tr>
  <td> value of Excel [https://support.office.com/zh-tw/article/DATEVALUE-%E5%87%BD%E6%95%B8-df8b07d4-7761-4a93-bc33-b7471bbff252 DATEVALUE] function <br />  (Excel: number of days since 1900/1/1) </td>
  <td> 1</td>
  <td> 25569</td>
  <td> 42370</td>
  <td> 5 digit numbers after 1970/1/1 </td>
</tr>
</table>
</div>
{{exclaim}} [https://support.office.com/zh-tw/article/value-%E5%87%BD%E6%95%B8-257d0108-07dc-437d-ae1c-bc2d3953d8c2 VALUE()] function on Excel is  number of days since 1900/1/1. [https://support.google.com/docs/answer/3094220?hl=en VALUE()] function on Google sheet is number of days since 1899/12/30 {{access | date=2019-05-22}}


=== now ===
=== now ===
Line 343: Line 315:


== List of binary safe functions ==
== List of binary safe functions ==
{| class="wikitable sortable"
[[List of PHP binary safe functions]]
|-
! Goal !! Binary safe function !! Binary NOT safe
|-
| "Return part of a string" || [https://www.php.net/manual/en/function.mb-substr.php mb_substr] || [https://www.php.net/manual/en/function.substr.php substr]
|-
| "Get string length" || [https://www.php.net/manual/en/function.mb-strlen.php mb_strlen] || [https://www.php.net/manual/en/function.strlen.php strlen]
|-
| "Replace all occurrences of the search string with the replacement string" || [https://www.php.net/manual/en/function.str-replace.php str_replace] ||
|-
| "Find the position of the first occurrence of a substring in a string" || [https://php.net/manual/en/function.strpos.php strpos] ||
|-
| 範例 || 範例 || 範例
|}


== trigger to make backup of deleted data before deleting them ==
== trigger to make backup of deleted data before deleting them ==
Line 430: Line 389:
[[Category:Data_hygiene]]
[[Category:Data_hygiene]]
[[Category:Data Science]]
[[Category:Data Science]]
[[Category:Time river]]

Revision as of 21:00, 28 July 2019

mulitple approaches to completing the same/similar task using PHP, MySQL and Excel

time / timestamp

now

human readable time format

Convert unix timestamp (the number of seconds since 1970/01/01) <--> to the human readable time format ex: 2024-03-28 10:36:06

  • php: echo date('Y-m-d H:i:s'); or 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-03-28 10:36:06' Icon_exclaim.gif server timezone dependent
  • mysql: Icon_exclaim.gif default client timezone is server timezone[1]
    • SELECT NOW(); Returns 2024-03-28 10:36:06 which mapping to DATETIME type[2]
    • SELECT NOW()+0; //2011-04-01 12:19:43 returns 20110401122023.000000
    • SELECT CURRENT_TIMESTAMP; Returns 2024-03-28 10:36:06 Icon_exclaim.gif 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');
  • BASH (UNIX shell script) with GUN date installed in Linux Os linux.png , Mac icon_os_mac.png or Cygwin of Win Os windows.png :
    • echo $(date '+%Y-%m-%d %H:%M:%S')[4] or echo `date +\%Y-\%m-\%d\ %H:%M:%S` 2>&1 or date +\%Y-\%m-\%d\ %H:%M:%S Returns 2024-03-28 10:36:06 Icon_exclaim.gif 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-03-28 10:36:06 Icon_exclaim.gif force to use Taipei timezone

current timestamp

The number of seconds since 1970/01/01. UTC±0 e.g. 2011-05-30 01:56:38 returns 1306720622

  • BASH for Linux Os linux.png & Mac icon_os_mac.png : echo $(date +%s) "%s - seconds since 1970-01-01 00:00:00 UTC"[5]
  • BASH for Mac icon_os_mac.png : (1) brew install coreutils (2) key-in gdate +%s[6]
  • php: echo time();
  • php: echo microtime(); PHP: microtime - Manual "Returns current Unix timestamp with microseconds" //ex: 0.45920500 1406776901
  • mysql: SELECT UNIX_TIMESTAMP();
  • javascript: getTime() Method ex: new Date().getTime(); //Return the number of milliseconds since 1970/01/01 [7]
  • 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
 */

How to print the number of nanoseconds or number of microseconds since 1970/01/01:

Unit Example Approach1: date Approach2: gdate[8]
(Human readable) 2018-11-04 13:55:52 echo $(date '+%Y-%m-%d %H:%M:%S') for Linux Os linux.png , Mac icon_os_mac.png & CygWin on Win Os windows.png echo $(gdate '+%Y-%m-%d %H:%M:%S') for Mac icon_os_mac.png
the number of seconds since 1970/01/01 1541310952 echo $(date '+%s') for Linux Os linux.png , Mac icon_os_mac.png & CygWin on Win Os windows.png echo $(gdate '+%s') for Mac icon_os_mac.png
the number of microseconds since 1970/01/01 1541310952.181870 printf "%.6f\n" $(date '+%s.%N') for Linux Os linux.png & CygWin on Win Os windows.png printf "%.6f\n" $(gdate '+%s.%N') for Mac icon_os_mac.png
the number of nanoseconds since 1970/01/01 1541310952.181870383 echo $(date '+%s.%N') for Linux Os linux.png & CygWin on Win Os windows.png echo $(gdate '+%s.%N') for Mac icon_os_mac.png

Icon_exclaim.gif Since echo $(date '+%N') was not supported on Mac icon_os_mac.png [9], alternative approach is the installation of gdate.

specified time

Convert the unix timestamp to human readable time format

ex: 2010-12-21 10:05:06

  • Epoch Converter - Unix Timestamp Converter
  • 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] Icon_exclaim.gif 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 Icon_exclaim.gif 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 Icon_exclaim.gif server timezone dependent
    • convert mysql datatype timestamp (UTC) to Taipei datetime (client timezone dependent) [10]
/* 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 [11]

Convert human-readable time to timestamp (string to timestamp)

  • PHP: Using strtotime() function ex: strtotime('2010-12-21 10:05:06') [12]
  • mysql: Using UNIX_TIMESTAMP() function
    • SELECT UNIX_TIMESTAMP('2011-03-15 18:53:57'); /* return timestamp: 1300186437 */ Icon_exclaim.gif server timezone dependent
    • SELECT UNIX_TIMESTAMP(STR_TO_DATE('2011-03-15 18:53:57', '%Y-%m-%d %H:%i:%S')); /* return timestamp: 1300186437 */ Icon_exclaim.gif server timezone dependent
  • Excel:
    • input data (A2 cell): YYYY/MM/DD (e.g. 2024/03/28 ) or YYYY/MM/DD HH:MM (e.g. 2024/03/28 10:36)
    • =(A2-DATE(1970,1,1))*86400 [13]

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: March 28, 2024 -> 2024-03-28

Previous month based on current month

  • PHP: echo date("Y-m", strtotime("-1 month")); // Returns YYYY-MM [15]
  • MySQL:
    • Returns YYYY-MM: Using DATE_FORMAT & DATE_ADD functions. SELECT DATE_FORMAT(NOW() - INTERVAL 1 MONTH, "%Y-%m"); [16]
    • Returns YYYY-MM-DD: SELECT DATE(NOW() - INTERVAL 1 MONTH);
  • Excel: Using EOMONTH() function. =YEAR(EOMONTH(NOW(), -2)+1)&"-"&TEXT(MONTH(EOMONTH(NOW(), -2)+1), "00") // Returns YYYY-MM [17]

today, yesterday or the day before yesterday

today

  • MySQL: SELECT CURRENT_DATE(); or SELECT CURDATE(); Returns 2024-03-28 [18]
  • EXCEL:
    • =TODAY() Returns 2024/03/28 [19]
    • =TEXT(TODAY(), "YYYY-MM-DD") Returns 2024-03-28 [20]
  • PHP: date('Y-m-d', TIME())
  • Shell script in Linux Os linux.png or Mac icon_os_mac.png with GUN date installed.
    • echo $(date +"%Y-%m-%d") or echo `date +\%Y-\%m-\%d` 2>&1 or date +"%Y-%m-%d" Returns 2024-03-28[21]
    • specify timezone: echo $(TZ=":Asia/Taipei" date +"%Y-%m-%d") or echo $(TZ=":US/Eastern" date +"%Y-%m-%d")[22]
// JavaScript
var date = new Date();
date;

// output: Thu Oct 18 2018 16:10:33 GMT+0800 (台北標準時間)

yesterday //Returns sample: 2024-03-27

  • MySQL:
    • SELECT DATE(CURRENT_TIMESTAMP - INTERVAL 1 DAY);
    • SELECT DATE_SUB(DATE( CURRENT_DATE() ), INTERVAL 1 DAY);
  • EXCEL: =TEXT(TODAY()-1, "YYYY-MM-DD") [23]
  • PHP: date('Y-m-d', strtotime("-1 days"))
  • Shell script in Linux Os linux.png with GUN date installed. Not work on Mac icon_os_mac.png
    • echo $(date -d "yesterday" '+%Y-%m-%d')[24]
    • date -d '-1 day' '+%Y-%m-%d' [25]
    • specify timezone: echo $(TZ=":Asia/Taipei" date -d "yesterday" '+%Y-%m-%d') or echo $(TZ=":US/Eastern" date -d "yesterday" '+%Y-%m-%d')
// JavaScript
var date = new Date();
date.setDate(date.getDate() - 1);
date;

// output: Wed Oct 17 2018 16:15:43 GMT+0800 (台北標準時間)

the day before yesterday //Returns sample: 2024-03-26

  • MySQL
    • SELECT DATE(CURRENT_TIMESTAMP - INTERVAL 2 DAY);
    • SELECT DATE_SUB(DATE( CURRENT_DATE() ), INTERVAL 2 DAY);
  • EXCEL: =TEXT(TODAY()-2, "YYYY-MM-DD")
  • PHP: date('Y-m-d', strtotime("-2 days"))
  • Shell script in Linux Os linux.png with GUN date installed. Not work on Mac icon_os_mac.png
    • date -d '-2 day' '+%Y-%m-%d' [26]


// JavaScript
var date = new Date();
date.setDate(date.getDate() - 2);
date;

// output: Wed Oct 16 2018 16:15:43 GMT+0800 (台北標準時間)

Day of week

Day of week

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 */ Icon_exclaim.gif time difference between two DATETIME values

timer

Timer

Date interval

When people said 2024-03-01 ~ 2024-03-03, it means 2024-03-01 00:00:00 ~ 2024-03-03 59:59:59 in the system.

Query by month

mysql ex: find the records in January, 2024[27] Data type of column `time` is datetime or timestamp (e.g.: 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' Icon_exclaim.gif 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'

Query by day

Purpose: find the records in 2024-03-28 (2024-03-28 00:00:00 ~ 2024-03-28 23:59:59)

  • SELECT * FROM `table_name` WHERE `time` >= '2024-03-28 00:00:00' AND `time` <= '2024-03-28 23:59:59'
  • SELECT * FROM `table_name` WHERE `time` BETWEEN '2024-03-28 00:00:00' AND '2024-03-28 23:59:59'
  • SELECT * FROM `table_name` WHERE DATE(`time`) = '2024-03-28'

date format

YYYY-MM-DD e.g. 2024-03-28 was converted from 2024-03-28 01:23:45.

YYYY-MM-01: First day of the specific month[28] e.g. 2024-03-01 was converted from 2024-03-28 01:23:45.

YYYYMM e.g. 202403 was converted from 2024-03-28 01:23:45.

further reading

Math

Format a number

Round a number downward to its nearest integer

other

  • a >0 OR b > 0 OR c > 0
  • a+b+c > 0


Text / String

Number of characters

Number of characters

text match

//PHP code snippet:
$array = array("AAA", "BBB", "CCC");

if(in_array($var, $array){
//do something
}
  • preg_match...
  • verbose approach: Icon_exclaim.gif IF (0 == 'String') always be true?[29]
if($var =="AAA" OR $var =="BBB" OR $var =="CCC"){
//do something
}


text input filter

List of binary safe functions

List of PHP binary safe functions

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


reload

  • Javascript
    • iframe (reload) - parent.location.reload();

find unique (non duplicated) data

  • SELECT DISTINCT column FROM table[30]
  • (1)SELECT * FROM table; (2)Using PHP: array_unique for data handling two or more columns
  • SELECT column FROM table GROUP BY column

export/import sql file

MySQL commands

reverse the boolean value

  • PHP:
$var = true;
print( !$var); //add ! symbol to reverse the boolean value

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

  1. Using phpMyAdmin or other MySQL client
  2. Input the SQL query:
    EXPLAIN table_name;
  3. Print view (with full texts)
  4. Easy to copy the table to other work processor
  5. (optional) Adjust the text size to 10

debug

SQL syntax debug

references

  1. MySQL :: MySQL 5.5 Reference Manual :: 10.6 MySQL Server Time Zone Support
  2. MySQL :: MySQL 5.1 Reference Manual :: 11.3.1 The DATE, DATETIME, and TIMESTAMP Types
  3. timezone - How do I get the current time zone of MySQL? - Stack Overflow
  4. date Man Page - Linux - SS64.com
  5. date(1): print/set system date/time - Linux man page
  6. gdate(1) - print or set the system date and time
  7. JavaScript getTime() Method
  8. macos - How to have GNU's date in OS X? - Ask Different
  9. macos - How to get Bash execution time in milliseconds under Mac OS X? - Super User
  10. timezone - How do I get the current time zone of MySQL? - Stack Overflow
  11. Excel Timestamp to Date ← Automate Everything
  12. How to convert date to timestamp in PHP? - Stack Overflow
  13. Excel date to Unix timestamp - Stack Overflow
  14. MySQL MONTHNAME() from numbers - Stack Overflow
  15. Getting last month's date in php - Stack Overflow
  16. php - MySQL first day and last day of current and previous month from date (no timestamp) - Stack Overflow
  17. Excel formula: Get first day of previous month | Exceljet
  18. MySQL :: MySQL 5.5 Reference Manual :: 12.7 Date and Time Functions
  19. How to change Excel date format and create custom formatting
  20. How to change Excel date format and create custom formatting
  21. Using the ‘date’ command in your crontab
  22. How can I have `date` output the time from a different timezone? - Unix & Linux Stack Exchange
  23. Formula yesterday's date
  24. 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
  25. shell - How to get yesterday and day before yesterday in linux? - Stack Overflow
  26. shell - How to get yesterday and day before yesterday in linux? - Stack Overflow
  27. sql - MySQL Query GROUP BY day / month / year - Stack Overflow
  28. mysql - How do I get the first day of the current month? - Stack Overflow
  29. Why does PHP consider 0 to be equal to a string? - Stack Overflow
  30. MySQL :: MySQL 5.0 Reference Manual :: 8.3.1.13 DISTINCT Optimization