Jump to content

PHP and MySQL syntax: Difference between revisions

From LemonWiki共筆
← Older editNewer edit →
Line 114: Line 114:


=== specified time ===
=== specified time ===
==== convert the unix timestamp to '''human readable''' time format ====
==== Convert the unix timestamp to '''human readable''' time format ====
ex: 2010-12-21 10:05:06
ex: 2010-12-21 10:05:06
* [http://www.epochconverter.com/ Epoch Converter - Unix Timestamp Converter]
* [http://www.epochconverter.com/ Epoch Converter - Unix Timestamp Converter]
Line 144: Line 144:
** {{kbd | key==( unix_time_stamp /86400)+DATE(1970,1,1) }} // convert the unix time stamp 1421539200 to the human readable time format 2015/1/18 <ref>[http://www.bajb.net/2010/05/excel-timestamp-to-date/ Excel Timestamp to Date ← Automate Everything]</ref>
** {{kbd | key==( unix_time_stamp /86400)+DATE(1970,1,1) }} // convert the unix time stamp 1421539200 to the human readable time format 2015/1/18 <ref>[http://www.bajb.net/2010/05/excel-timestamp-to-date/ Excel Timestamp to Date ← Automate Everything]</ref>


==== convert human-readable time to '''timestamp''' ====
==== Convert human-readable time to '''timestamp''' ====
* PHP: using [http://php.net/manual/en/function.strtotime.php strtotime()] function ex: strtotime('2010-12-21 10:05:06') <ref>[http://stackoverflow.com/questions/113829/how-to-convert-date-to-timestamp-in-php How to convert date to timestamp in PHP? - Stack Overflow]</ref>
* PHP: using [http://php.net/manual/en/function.strtotime.php strtotime()] function ex: strtotime('2010-12-21 10:05:06') <ref>[http://stackoverflow.com/questions/113829/how-to-convert-date-to-timestamp-in-php How to convert date to timestamp in PHP? - Stack Overflow]</ref>
* mysql:  
* mysql:  
Line 161: Line 161:
* MySQL: [http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date STR_TO_DATE(str,format)] ex: {{kbd | key=SELECT STR_TO_DATE('{{CURRENTMONTHNAME}} {{CURRENTDAY}}, {{CURRENTYEAR}}','%M %d, %Y');}}<ref>[http://stackoverflow.com/questions/7027129/mysql-monthname-from-numbers MySQL MONTHNAME() from numbers - Stack Overflow]</ref>
* MySQL: [http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date STR_TO_DATE(str,format)] ex: {{kbd | key=SELECT STR_TO_DATE('{{CURRENTMONTHNAME}} {{CURRENTDAY}}, {{CURRENTYEAR}}','%M %d, %Y');}}<ref>[http://stackoverflow.com/questions/7027129/mysql-monthname-from-numbers MySQL MONTHNAME() from numbers - Stack Overflow]</ref>
* Excel
* Excel
==== Previous month based on current month ====
* PHP
* MySQL
* Excel: {{kbd | key = <nowiki>=YEAR(EOMONTH(NOW(), -2)+1)&"-"&TEXT(MONTH(EOMONTH(NOW(), -2)+1), "00")</nowiki>}} // Returns YYYY-MM <ref>[https://exceljet.net/formula/get-first-day-of-previous-month Excel formula: Get first day of previous month | Exceljet]</ref>


=== PHP microtime to MySQL timestamp ===
=== PHP microtime to MySQL timestamp ===

Revision as of 11:28, 5 June 2017

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-09-05 22:42:06 

/* 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 , macOS icon_os_mac.png or Cygwin of Win :
    • echo `date +\%Y-\%m-\%d\ %H:%M:%S` 2>&1 or date +\%Y-\%m-\%d\ %H:%M:%S Returns 2026-09-05 22:42: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 2026-09-05 22:42:06 Icon_exclaim.gif force to use Taipei timezone

current timestamp

the number of seconds since 1970/01/01. UTC±0

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 or the day before yesterday

today

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


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

  • MySQL:
    • SELECT DATE(CURRENT_TIMESTAMP - INTERVAL 1 DAY);
    • SELECT DATE_SUB(DATE( CURRENT_DATE() ), INTERVAL 1 DAY);
  • PHP: date('Y-m-d', strtotime("-1 days"))

the day before yesterday //Returns sample: 2026-09-{{#time: d |now - 48 hours}}

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) <ref>timezone - How do I get the current time zone of MySQL? - Stack Overflow</ref>
/* 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`

Convert human-readable time to timestamp

  • PHP: using strtotime() function ex: strtotime('2010-12-21 10:05:06') <ref>How to convert date to timestamp in PHP? - Stack Overflow</ref>
  • mysql:
    • 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:

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: September 5, 2026 -> 2026-09-5

Previous month based on current month

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 2026-09-01 ~ 2026-09-03, it means 2026-09-01 00:00:00 ~ 2026-09-03 59:59:59 in the system.

Query by month

mysql ex: find the records in January, 2026<ref>sql - MySQL Query GROUP BY day / month / year - Stack Overflow</ref> 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`) = '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' Icon_exclaim.gif 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'

date format

YYYY-MM-DD e.g. 2026-09-05 was converted from 2026-09-05 01:23:45.

YYYY-MM-01: First day of the specific month<ref>mysql - How do I get the first day of the current month? - Stack Overflow</ref> e.g. 2026-09-01 was converted from 2026-09-05 01:23:45.

YYYYMM e.g. 202609 was converted from 2026-09-05 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

length of characters

echo mb_strlen("《王大文 Dawen》", 'UTF-8'); // return 11
echo strlen("《王大文 Dawen》"); // return 21

SELECT CHAR_LENGTH("《王大文 Dawen》"); /* return 11 */
SELECT LENGTH("《王大文 Dawen》"); /* return 21 */

=LEN("《王大文 Dawen》") // return 11

text match

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

if(in_array($var, $array){
//do something
}
  • preg_match...
if($var =="AAA" OR $var =="BBB" OR $var =="CCC"){
//do something
}


text input filter

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

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

<references/>

Retrieved from "https://wiki.planetoid.info/index.php?title=PHP_and_MySQL_syntax&oldid=18189"