mysql时间校验 函数

CREATE 
	OR REPLACE FUNCTION isnumeric ( txtStr VARCHAR ) RETURNS BOOLEAN AS $$ BEGIN
		RETURN txtStr ~ '^([0-9]+[.]?[0-9]*|[.][0-9]+)$';
	
END; 
$$ LANGUAGE'plpgsql';
CREATE 
	OR REPLACE FUNCTION isDate ( dateStr VARCHAR ) RETURNS BOOLEAN AS $$ BEGIN
	IF
		( dateStr IS NULL ) THEN
			RETURN FALSE;
		
	END IF;
	PERFORM dateStr :: TIMESTAMP;
	RETURN TRUE;
	EXCEPTION 
	WHEN OTHERS THEN
		RETURN FALSE;
	
END; 
$$ LANGUAGE'plpgsql';
SELECT SUM
	( chargeTime ) AS chargeTime,
	equipmentId,
	operatorName 
FROM
	(
	SELECT
		tmp1.chargeTime AS chargeTime,
		tmp2.equipment_id AS equipmentId,
		tmp2.operator_name AS operatorName 
	FROM
		(
		SELECT
			( EXTRACT ( epoch FROM CAST ( end_time AS TIMESTAMP ) ) - EXTRACT ( epoch FROM CAST ( start_time AS TIMESTAMP ) ) ) AS chargeTime,
			start_time,
			end_time,
			connector_id,
			operator_id 
		FROM
			charge_order_info 
		WHERE
			start_time >= '2020-12-01 00:00:00' 
			AND start_time < '2021-01-01 00:00:00' 
			AND isnumeric ( start_time ) = FALSE 
			AND isnumeric ( end_time ) = FALSE 
			AND isDate ( start_time ) = TRUE 
			AND isDate ( end_time ) = TRUE 
		) tmp1,
		(
		SELECT
			cci.equipment_id AS equipment_id,
			cci.connector_id AS connector_id,
			cci.operator_id AS operator_id,
			coi.operator_name AS operator_name 
		FROM
			charge_connector_info cci,
			charge_operator_info coi 
		WHERE
			cci.operator_id = coi.operator_id 
		) tmp2 
	WHERE
		tmp1.connector_id = tmp2.connector_id 
	) tmp3 
GROUP BY
	equipmentId,
	operatorName 
ORDER BY
	equipmentId DESC