Validate the datetime value

From LemonWiki共筆
Revision as of 17:28, 13 August 2020 by Unknown user (talk) (Created page with "Verify the value by using MySQL functions: Verify the value should be year-month-day format e.g. {{Template:Today}} == MySQL approach == <pre> SELECT `my_date_column`, UNIX_...")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

Verify the value by using MySQL functions:

Verify the value should be year-month-day format e.g. 2024-03-29

MySQL approach

SELECT `my_date_column`, UNIX_TIMESTAMP(STR_TO_DATE(`my_date_column`, '%Y-%m-%d')) 
FROM `my_table`
WHERE
UNIX_TIMESTAMP(STR_TO_DATE(`my_date_column`, '%Y-%m-%d')) IS NULL;

Verify the value should be year/month/day format

SELECT `my_date_column`, UNIX_TIMESTAMP(STR_TO_DATE(`my_date_column`, '%Y/%m/%d')) 
FROM `my_table`
WHERE
UNIX_TIMESTAMP(STR_TO_DATE(`my_date_column`, '%Y/%m/%d')) IS NULL;

Verify the value should be hour:minute:second format e.g. 10:43:06

SELECT `my_time_column`, UNIX_TIMESTAMP(STR_TO_DATE(`my_time_column`, '%H:%i:%S')) 
FROM `my_table`
WHERE
UNIX_TIMESTAMP(STR_TO_DATE(`my_time_column`, '%H:%i:%S')) IS NULL;

Verify the value should be year-month-day hour:minute:second format e.g. 2024-03-29 10:43:06

SELECT `my_time_column`, UNIX_TIMESTAMP(STR_TO_DATE(`my_time_column`, '%Y-%m-%d %H:%i:%S')) 
FROM `my_table`
WHERE
UNIX_TIMESTAMP(STR_TO_DATE(`my_time_column`, '%Y-%m-%d %H:%i:%S')) IS NULL;

Alternative PHP solution: strtotime

references