PHP and MySQL syntax: Difference between revisions

Latest revision as of 19:11, 26 April 2023

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

Date time / unix timestamp[edit]

now[edit]

human readable time format[edit]

Convert unix timestamp (the number of seconds since 1970/01/01) <--> to the human readable time format ex: 2024-04-28 20:03: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-04-28 20:03:06' server timezone dependent
mysql: default client timezone is server timezone^[1]
- SELECT NOW(); Returns 2024-04-28 20:03: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 20:03: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');

BASH (UNIX shell script) with GUN date installed in Linux , Mac or Cygwin of Win :
- 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-04-28 20:03:06 server timezone dependent
- date=`date -I`;echo $date; returns 2024-04-28 on Linux But it was not supported on Mac

and it showed the error message "illegal option -- I"

current timestamp[edit]

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

BASH for Linux & Mac : echo $(date +%s) "%s - seconds since 1970-01-01 00:00:00 UTC"^[5]
BASH for Mac : (1) brew install coreutils (2) key-in gdate +%s^[6]
php: echo time();
mysql: SELECT UNIX_TIMESTAMP();
EXCEL: Excel date to Unix timestamp - Stack Overflow

current Unix timestamp with microseconds

php: echo microtime(); PHP: microtime - Manual "Returns current Unix timestamp with microseconds" //ex: 0.45920500 1406776901

current Unix timestamp in milliseconds

javascript: getTime() Method ex: new Date().getTime(); //Return the number of milliseconds since 1970/01/01 ^[7]

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 , Mac & CygWin on Win	echo $(gdate '+%Y-%m-%d %H:%M:%S') for Mac
the number of seconds since 1970/01/01	1541310952	echo $(date '+%s') for Linux , Mac & CygWin on Win	echo $(gdate '+%s') for Mac
the number of microseconds since 1970/01/01	1541310952.181870	printf "%.6f\n" $(date '+%s.%N') for Linux & CygWin on Win	printf "%.6f\n" $(gdate '+%s.%N') for Mac
the number of nanoseconds since 1970/01/01	1541310952.181870383	echo $(date '+%s.%N') for Linux & CygWin on Win	echo $(gdate '+%s.%N') for Mac

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

Specified time[edit]

Convert the unix timestamp to human readable time format[edit]

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] 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) ^[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)[edit]

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 */ 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 20:03)
- =(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: April 28, 2024 -> 2024-04-28

PHP:
MySQL: STR_TO_DATE(str,format) ex: SELECT STR_TO_DATE('April 28, 2024', '%M %d, %Y'); // Returns 2024-04-28 ^[14]
Excel

Convert the datetime to ISO foamt time format[edit]

Using the DateTime::createFromFormat^[15]

$date = DateTime::createFromFormat('d/m/Y', "29/03/2021");
echo $date->format('Y-m-d');
// output: 2021-03-29

$date = DateTime::createFromFormat('M d D Y H:i', "Jun 04 Sun 2023 08:25");
echo $date->format('Y-m-d H:i:s');
// output: 2023-06-04 08:25:00

The following instruction written by ChatGPT

// The input datetime string
$input = "Jun 04 Sun 2023 08:25";

// Specify the input format
$inputFormat = "M d D Y H:i";

// Create a DateTime object from the input string
$dateTime = DateTime::createFromFormat($inputFormat, $input);

// Check if the DateTime object was created successfully
if ($dateTime === false) {
    echo "Failed to parse the input datetime string.";
} else {
    // Format the DateTime object into the desired ISO format
    $isoFormat = "Y-m-d H:i:s";
    $output = $dateTime->format($isoFormat);

    // Print the output
    echo $output; // "2023-06-04 08:25:00"
}

Convert date time from Zulu format[edit]

Convert date time from zulu format e.g. 2024-04-28T10:59:39.000Z

Previous month based on current month[edit]

PHP: echo date("Y-m", strtotime("-1 month")); // Returns YYYY-MM ^[16]
MySQL:
- Returns YYYY-MM: Using DATE_FORMAT & DATE_ADD functions. SELECT DATE_FORMAT(NOW() - INTERVAL 1 MONTH, "%Y-%m"); ^[17]
- 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 ^[18]

today, yesterday or the day before yesterday[edit]

today

MySQL: SELECT CURRENT_DATE(); or SELECT CURDATE(); Returns 2024-04-28 ^[19]

EXCEL:
- =TODAY() Returns 2024/04/28 ^[20]
- =TEXT(TODAY(), "YYYY-MM-DD") Returns 2024-04-28 ^[21]

PHP: date('Y-m-d') or date('Y-m-d', TIME())

Javascript: JavaScript Date Reference [Last visited: 2018-10-18]

Shell script in Linux or Mac with GUN date installed.
- echo $(date +"%Y-%m-%d") or echo `date +\%Y-\%m-\%d` 2>&1 or date +"%Y-%m-%d" Returns 2024-04-28^[22]
- specify timezone: echo $(TZ=":Asia/Taipei" date +"%Y-%m-%d") or echo $(TZ=":US/Eastern" date +"%Y-%m-%d")^[23]

// JavaScript
var date = new Date();
date;

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

yesterday //Returns sample: 2024-04-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") ^[24]

PHP: date('Y-m-d', strtotime("-1 days"))

JavaScript: Calculate the date yesterday in JavaScript - Stack Overflow [Last visited: 2018-10-18]

Shell script in Linux with GUN date installed. Not work on Mac
- echo $(date -d "yesterday" '+%Y-%m-%d')^[25]
- date -d '-1 day' '+%Y-%m-%d' ^[26]
- 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-04-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 with GUN date installed. Not work on Mac
- date -d '-2 day' '+%Y-%m-%d' ^[27]

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

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

Day of week[edit]

Day of week

PHP microtime to MySQL timestamp[edit]

PHP microtime to MySQL timestamp

time difference of two time values[edit]

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

timer[edit]

Timer

Date interval[edit]

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[edit]

mysql ex: find the records in January, 2024^[28] 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' 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[edit]

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

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

Days before[edit]

Days before

date format[edit]

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

Using MySQL DATE() Function e.g. SELECT DATE( datetime_column );
Using MySQL DATE_FORMAT() Function e.g. SELECT DATE_FORMAT( datetime_column, '%Y-%m-%d');

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

Using MySQL DATE_FORMAT() Function e.g. SELECT DATE_FORMAT( datetime_column, '%Y-%m-01');
Using MySQL DATE_SUB() Function e.g. SELECT DATE_SUB(DATE( datetime_column ), INTERVAL DAYOFMONTH(DATE( datetime_column ))-1 DAY);
Using MySQL CONCAT() function & MySQL DATE_FORMAT() Function e.g. SELECT CONCAT( DATE_FORMAT( datetime_column, '%Y-%m' ) , '-01' ) ;

YYYYMM e.g. 202404 was converted from 2024-04-28 01:23:45.

Using MySQL EXTRACT() function e.g. SELECT EXTRACT(YEAR_MONTH FROM datetime_column);
Using MySQL DATE_FORMAT() Function e.g. SELECT DATE_FORMAT( datetime_column, '%Y%m');

Math[edit]

Format a number[edit]

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[edit]

other[edit]

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

Text / String[edit]

Number of characters[edit]

Count number of characters

text match[edit]

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?^[30]

if($var =="AAA" OR $var =="BBB" OR $var =="CCC"){
//do something
}

text input filter[edit]

List of binary safe functions[edit]

List of PHP binary safe functions

trigger to make backup of deleted data before deleting them[edit]

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[edit]

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[edit]

SELECT DISTINCT column FROM table^[31]
(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 the SQL file[edit]

MySQL commands

Export/Import the Excel file[edit]

Database management tools

reverse the boolean value[edit]

PHP:

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

EXCEL: NOT 函數 - Excel ex: =NOT(TRUE) will return FALSE

ideas[edit]

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[edit]

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[edit]

SQL syntax debug

References[edit]

[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] te Man Page - Linux - SS64.com

[5] te(1): print/set system date/time - Linux man page

[6] te(1) - print or set the system date and time

[7] JavaScript getTime() Method

[8] s - How to have GNU's date in OS X? - Ask Different

[9] s - 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] PHP convert date format dd/mm/yyyy => yyyy-mm-dd - Stack Overflow

[16] Getting last month's date in php - Stack Overflow

[17] - MySQL first day and last day of current and previous month from date (no timestamp) - Stack Overflow

[18] Excel formula: Get first day of previous month | Exceljet

[19] MySQL :: MySQL 5.5 Reference Manual :: 12.7 Date and Time Functions

[20] How to change Excel date format and create custom formatting

[21] How to change Excel date format and create custom formatting

[22] Using the ‘date’ command in your crontab

[23] How can I have `date` output the time from a different timezone? - Unix & Linux Stack Exchange

[24] Formula yesterday's date

[25] 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

[26] shell - How to get yesterday and day before yesterday in linux? - Stack Overflow

[27] shell - How to get yesterday and day before yesterday in linux? - Stack Overflow

[28] sql - MySQL Query GROUP BY day / month / year - Stack Overflow

[29] ysql - How do I get the first day of the current month? - Stack Overflow

[30] Why does PHP consider 0 to be equal to a string? - Stack Overflow

[31] MySQL :: MySQL 5.0 Reference Manual :: 8.3.1.13 DISTINCT Optimization

[1]

[2]

[3]

[4]

[5]

[6]

[7]

[8]

[9]

[10]

[11]

[12]

[13]

[14]

[15]

[16]

[17]

[18]

[19]

[20]

[21]

[22]

[23]

[24]

[25]

[26]

[27]

[28]

[29]

[30]

[31]