inserting a null value to Time type

[rodrigo21]

Hello,

I have a Time type column on a table. I have set it to ¨no null¨. When I enter a null value the column stores a 00:00:00 value.

How can I do to store just an empty value, so that the user have the choice to don't enter any time and display no time when the record is displayed?
Actually, the important part for me is displaying no value, I really don't mind what it is stored, but I dont want to loose the option of storing a ¨midnight value.


thanks,

[amitpatel66]

Hello,

I have a Time type column on a table. I have set it to ¨no null¨. When I enter a null value the column stores a 00:00:00 value.

How can I do to store just an empty value, so that the user have the choice to don't enter any time and display no time when the record is displayed?
Actually, the important part for me is displaying no value, I really don't mind what it is stored, but I dont want to loose the option of storing a ¨midnight value.


thanks,

You cannot store NULL in to NOT NULL column. And Since its TIME datatype, you cannot store something like White Spaces as well. You need to make the column to accept NULL values as well.

[ronverdonk]

Be careful: in a db a null value is not identical to a 0 (zero) value!

Null indicates the absence of a value, where 0 is a real value. Midnight is a zero value 00:00:00 and not a null value. Allowing a null in your time field indicates that you want the option of no value in your time field.

Ronald

[rodrigo21]

You cannot store NULL in to NOT NULL column. And Since its TIME datatype, you cannot store something like White Spaces as well. You need to make the column to accept NULL values as well.

Ok, I have make the colum accept NULL values, and then?
There must be some way for achieving this(not storing, but dislaying a null value for a Time datatype when the user has fill in a time form with ¨no time fot this trip¨ for example)

[rodrigo21]

Be careful: in a db a null value is not identical to a 0 (zero) value!

Null indicates the absence of a value, where 0 is a real value. Midnight is a zero value 00:00:00 and not a null value. Allowing a null in your time field indicates that you want the option of no value in your time field.

Ronald

Correct, but when inserting a null value I get 00:00:00 stored.

[amitpatel66]

Correct, but when inserting a null value I get 00:00:00 stored.

Thats becuase it cannot accept NULL value. Remove the CONSTRAINT NOT NULL from the column then it will be able to store NULL values

[rodrigo21]

Thats becuase it cannot accept NULL value. Remove the CONSTRAINT NOT NULL from the column then it will be able to store NULL values

Sure, all ready done that and still getting 00:00:00. Thats the problem

[ronverdonk]

Ok, I have make the colum accept NULL values, and then?
There must be some way for achieving this(not storing, but dislaying a null value for a Time datatype when the user has fill in a time form with ¨no time for this trip¨ for example)

When you allow null in your column and select it, the result of the select will return the literal NULL. If you do not want that literal but e.g. the literal 'empty', then replace it in your query like[code=mysql]select field1, IF(field2 IS NULL, 'empty ', field2) as field2 FROM table_name;[/code]Ronald

[ronverdonk]

Sure, all ready done that and still getting 00:00:00. Thats the problem

Set your default to NULL and all new records will have this when no time is filled in. The existing records however, keep the value they have.
So you wilkl have to UPDATE these separately.

Ronald

[rodrigo21]

When you allow null in your column and select it, the result of the select will return the literal NULL. If you do not want that literal but e.g. the literal 'empty', then replace it in your query like[code=mysql]select field1, IF(field2 IS NULL, 'empty ', field2) as field2 FROM table_name;[/code]Ronald

I have set the default to NULL and the column to accept null values, still storing 00:00:00. As the Mysql guide says a Time field cant store a null value, but their must be some coding to be done for solving this problem.

[ronverdonk]

But you do not want an empty field to show when you mean 00:00:00, i.e. 12 midnight, do you? If not, you just format your time field like this[code=mysql]SELECT field1, IF(field2 IS NULL, 'empty ', TIME_FORMAT(fie ld2,'%h:%m:%s') ) as field2 FROM table_name;[/code]and you see the difference in your output: '00:00:00' for midnight, 'empty' for null..

Ronald

[rodrigo21]

But you do not want an empty field to show when you mean 00:00:00, i.e. 12 midnight, do you? If not, you just format your time field like this[code=mysql]SELECT field1, IF(field2 IS NULL, 'empty ', TIME_FORMAT(fie ld2,'%h:%m:%s') ) as field2 FROM table_name;[/code]and you see the difference in your output: '00:00:00' for midnight, 'empty' for null..

Ronald

sorry if this questions are to basic:

I have just talk about one time field, so what would be field1 and field2 in your code?

How do I insert that code in phpMyadmin for example, I bet it is not a query

I haven't been able to store a null value on the field to make true the if condition, but I want to try it anyway if you could answer this maybe dumb questions.


Thanks in advance,

[ronverdonk]

sorry if this questions are to basic:

I have just talk about one time field, so what would be field1 and field2 in your code?

How do I insert that code in phpMyadmin for example, I bet it is not a query

I haven't been able to store a null value on the field to make true the if condition, but I want to try it anyway if you could answer this maybe dumb questions.

Thanks in advance,

It is just an example. You have to rework it for use with your own table. In teh example field2 is the field that holds the time (or null).

Lucky you did not bet, of course it is an MySQL query. Just enter that in the SQL command popup of phpMyAdmin and hit 'start'.

To change a field value in a row you just enter the UPDATE query (mind: it is an example, you must change it to your own tablename and fields) like[[code=mysql]update table_name set time_field=null where other_field='xx x';[/code]Ronald

[rodrigo21]

I will try to order things up a little to make things clear about this post an its progress

1)Time type field cant store a null value, instead it stores 00:00:00 value.

2)I have a time form that gives the user the choice of "I am not sure of the time yet".

3)A code on the query could display an empty value when it found a 00:00:00 value stored in the database

4)By doing this, if the user enters in the form a midnight value or 00:00:00, the query would also display an empty value, and that is not wanted.

Hope to make things clearer and start suggesting solutions from now on.

Thanks again

[docdiesel]

Hi,

to start with, I'm not a MySQL expert but I'm used to work with DB2. So it sounds a bit unusual to me that a database should not be able to store a NULL in a date field. But after reading the thread I'm suspecting somebody/something else to be betraying you.

As fas as I understand you're working with phpMyAdmin as frontend to MySQL. Did you check your problem with the command line mysql tool? For it could be that phpMyAdmin is converting the NULL into '00:00:00' when inserting without telling you.

I'd suggest to use the command line for a test. Try the following sql :

create table testtable (when time);
insert into testtable (when) values ('10:20:30');
insert into testtable (when) values (0);
insert into testtable (when) values (NULL);
select * from testtable;

and see what output it's generating.

If this is still wrong, at least there could be a workaround. Store the time as integer value (which MySQL HAS to allow to be NULL) using the seconds since midnight.

Regards,

Bernd