PHP and MySQL syntax

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	notes
value of unix timestamp (the number of seconds since 1970/01/01)	-2209075200	0	1451606400
value of Excel DATEVALUE function (number of days since 1900/1/1)	1	25569	42370	5 digit numbers after 1970/1/1

now

human readable time format

Convert unix timestamp (the number of seconds since 1970/01/01) <--> to the human readable time format ex: 2026-10-18 21:07: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 '2026-10-18 21:07:06' server timezone dependent
• mysql: default client timezone is server timezone
  • SELECT NOW(); Returns 2026-10-18 21:07:06 which mapping to DATETIME type
  • SELECT NOW()+0; //2011-04-01 12:19:43 returns 20110401122023.000000
  • SELECT CURRENT_TIMESTAMP; Returns 2026-10-18 21:07:06 Current time zone of MySQL is SYSTEM & client timezone dependent.
  • convert mysql datatype timestamp (UTC) to Taipei datetime (client timezone dependent)

/* 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 , macOS or Cygwin of Win :
  • echo $(date '+%Y-%m-%d %H:%M:%S') or echo `date +\%Y-\%m-\%d\ %H:%M:%S` 2>&1 or date +\%Y-\%m-\%d\ %H:%M:%S Returns 2026-10-18 21:07: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 2026-10-18 21:07:06 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 & macOS : echo $(date +%s) "%s - seconds since 1970-01-01 00:00:00 UTC"
• BASH for macOS : (1) brew install coreutils (2) key-in gdate +%s
• 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
• 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
 */

Unit	Example	Approach1: date	Approach2: gdate
(Human readable)	2018-11-04 13:55:52	echo $(date '+%Y-%m-%d %H:%M:%S') for Linux , macOS	echo $(gdate '+%Y-%m-%d %H:%M:%S') for macOS
the number of seconds since 1970/01/01	1541310952	echo $(date '+%s') for Linux , macOS	echo $(gdate '+%s') for macOS
the number of microseconds since 1970/01/01	1541310952.181870	printf "%.6f\n" $(date '+%s.%N') for Linux	printf "%.6f\n" $(gdate '+%s.%N') for macOS
the number of nanoseconds since 1970/01/01	1541310952.181870383	echo $(date '+%s.%N') for Linux	echo $(gdate '+%s.%N') for macOS

Since echo $(date '+%N') was not supported on macOS , 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] 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)

/* 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

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

• PHP: using strtotime() function ex: strtotime('2010-12-21 10:05:06')
• 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. 2026/10/18 ) or YYYY/MM/DD HH:MM (e.g. 2026/10/18 21:07)
  • =(A2-DATE(1970,1,1))*86400

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: October 18, 2026 -> 2026-10-18

• PHP:
• MySQL: STR_TO_DATE(str,format) ex: SELECT STR_TO_DATE('October 18, 2026', '%M %d, %Y'); // Returns 2026-10-18
• Excel

Previous month based on current month

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

today, yesterday or the day before yesterday

today

• MySQL: SELECT CURRENT_DATE(); or SELECT CURDATE(); Returns 2026-10-18

• EXCEL:
  • =TODAY() Returns 2026/10/18
  • =TEXT(TODAY(), "YYYY-MM-DD") Returns 2026-10-18

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

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

• Shell script in Linux or macOS with GUN date installed.
  • echo $(date +"%Y-%m-%d") or echo `date +\%Y-\%m-\%d` 2>&1 or date +"%Y-%m-%d" Returns 2026-10-18
  • specify timezone: echo $(TZ=":Asia/Taipei" date +"%Y-%m-%d") or echo $(TZ=":US/Eastern" date +"%Y-%m-%d")

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

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

yesterday //Returns sample: 2026-10-{{#time: d |now - 24 hours}}

• MySQL:
  • SELECT DATE(CURRENT_TIMESTAMP - INTERVAL 1 DAY);
  • SELECT DATE_SUB(DATE( CURRENT_DATE() ), INTERVAL 1 DAY);

• EXCEL: =TEXT(TODAY()-1, "YYYY-MM-DD")

• 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 macOS
  • echo $(date -d "yesterday" '+%Y-%m-%d')
  • date -d '-1 day' '+%Y-%m-%d'
  • 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: 2026-10-{{#time: d |now - 48 hours}}

• 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 macOS
  • date -d '-2 day' '+%Y-%m-%d'

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

timer

Timer

Date interval

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

Query by month

mysql ex: find the records in January, 2026 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`) = '2026' AND MONTH(`time`) = '1' /*MONTH(`time`) = '1' or MONTH(`time`) = '01' are both ok*/
• SELECT * FROM `table_name` WHERE DATE_FORMAT(`time`, '%Y-%m') = '2026-01'
• SELECT * FROM `table_name` WHERE `time` LIKE '2026-01%' /*find the time records starting with the string 2026-01 */
• SELECT * FROM `table_name` WHERE DATE(`time`) between '2026-01-01' AND '2026-01-31' add the DATE function to the `time` field
• SELECT * FROM `table_name` WHERE `time` >= '2026-01-01 00:00:00' AND `time` < '2026-02-01 00:00:00'
• SELECT * FROM `table_name` WHERE `time` >= '2026-01-01 00:00:00' AND `time` <= '2026-01-31 59:59:59'

Query by day

Purpose: find the records in 2026-10-18 (2026-10-18 00:00:00 ~ 2026-10-18 23:59:59)

• SELECT * FROM `table_name` WHERE `time` >= '2026-10-18 00:00:00' AND `time` <= '2026-10-18 23:59:59'
• SELECT * FROM `table_name` WHERE `time` BETWEEN '2026-10-18 00:00:00' AND '2026-10-18 23:59:59'
• SELECT * FROM `table_name` WHERE DATE(`time`) = '2026-10-18'

date format

YYYY-MM-DD e.g. 2026-10-18 was converted from 2026-10-18 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 e.g. 2026-10-01 was converted from 2026-10-18 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. 202610 was converted from 2026-10-18 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');

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

• PHP: round - Manual
• JavaScript floor() Method
• ROUND 函數 - Excel - Office.com

other

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

Text / String

Number of characters

Example of string	Number of characters	Number of bytes
fox	3	3
The quick brown fox jumps over the lazy dog	43	43
狐	1	2
敏捷的棕毛狐狸從懶狗身上躍過	14	28

• PHP: PHP mb_strlen function

// number of characters
echo mb_strlen("狐", 'UTF-8') . PHP_EOL; // return 1
echo mb_strlen("《王大文 Dawen》", 'UTF-8') . PHP_EOL; // return 11

// number of bytes
echo strlen("狐") . PHP_EOL; // return 3
echo strlen("《王大文 Dawen》") . PHP_EOL; // return 21

• MySQL: MySQL CHAR_LENGTH() function

// number of characters
SELECT CHAR_LENGTH("狐"); /* return 1 */
SELECT CHAR_LENGTH("《王大文 Dawen》"); /* return 11 */

// number of bytes
SELECT LENGTH("狐"); /* return 3 */
SELECT LENGTH("《王大文 Dawen》"); /* return 21 */

• MySQL :: MySQL 8.0 Reference Manual :: 11.4.1 The CHAR and VARCHAR Types e.g. VARCHAR(5) or CHAR(5) means can hold up to 5 characters.
• Excel: LEN, LENB functions / LEN、LENB 函數

// number of characters
=LEN("狐") // return 1
=LEN("《王大文 Dawen》") // return 11

// number of bytes
=LENB("狐") // return 2
=LENB("《王大文 Dawen》") // return 16

• Calculate String Length Online
• string - How many bytes does one Unicode character take? - Stack Overflow

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?

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

text input filter

• PHP: PDO::prepare + PDOStatement::bindParam
• PHP: mysqli::real_escape_string

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
• (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

• 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

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