mysql - How to set the default value for only positive time -

i have column time type(00:00:00). want allow positive values. so, -05:30:00, example, invalid.

is there way of doing it?

i want set default value column, if recieve '-05:00:00' value set value '00:00:00'.

you can depend on time_format( time, format ) , if required on date_format( date, format ) functions in mysql. require substring , locate functions parse.

to set time field default or +ve part of time data, following examples helps you:

mysql> set @time_value='-23:51:48'; query ok, 0 rows affected (0.00 sec) 

example 1:

mysql> insert          time_table( time_field )          values ( if( locate( '-', @time_value ) = 1, '00:00:00', @time_value ) ); query ok, 1 row affected (0.00 sec)   

let see inserted:

mysql> select time_field time_table; +------------+ | time_field | +------------+ | 00:00:00   | +------------+ 1 row in set (0.00 sec) 

example 2:

mysql> insert          time_table( time_field )          values ( substring( @time_value, ( locate( '-', @time_value ) + 1 ) ) ); query ok, 1 row affected (0.00 sec)   

example 3:

mysql> insert          time_table( time_field )          values           ( substring( time_format( @time_value, '%h %k:%i:%s' ),                        ( locate( ' ', time_format( @time_value, '%h %k:%i:%s' ) ) + 1 )                      )            ); query ok, 1 row affected (0.00 sec)   

in this:

1. h: hour return 00 23
2. k: hour return 0 23
3. i: minutes, numeric 00 59
4. s: seconds return 00 59

let see inserted:

mysql> select time_field time_table; +------------+ | time_field | +------------+ | 23:51:48   | +------------+ 1 row in set (0.00 sec) 

for more details on format specifier characters %k refer date_format( date, format )