Cool date converting

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Cool date converting

Jolle Carlestam-2
In the Lasso chat room there's right now a discussion on how to handle dates when retrieved from a mysql table.

Problem being that converting an input to a date object can be unpredictable depending on the type of value thats fed to the date type.

Some examples:
date(null)
date(string)
date(0.0)
->
01/01/0001 00:00:00
2013-12-18 20:15:57
1970-01-01 01:00:00

Three different dates returned, and none of them the desired outcome.
Yet all of the input values are possible and legitimate.

In the discussion, that's still ongoing, Kyle came up with a one row solution that would make sure that dates values indeed are converted to date objects and the rest will be converted to null.

define dateornull(n) => #n and date(#n) or null

Example usage:
dateornull(void)
dateornull(null)
dateornull('2013-12-18')
dateornull(1387389600.000000)
dateornull(void) -> type
dateornull(null) -> type
dateornull('2013-12-18') -> type
dateornull(1387389600.000000) -> type
->


2013-12-18
2013-12-18 19:00:00
null
null
date
date

Thus no more unexpected date values returned. If the input is not a value meant to be a date, null is returned. I all other cases a date object is returned.

Figured I'd share since not all of you hang in the chat room.

HDB
Jolle

#############################################################
This message is sent to you because you are subscribed to
  the mailing list Lasso
[hidden email]
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Cool date converting

Jussi Hirvi-2
On 18.12.2013 21.24, Jolle Carlestam wrote:
> In the Lasso chat room there's right now a discussion on how to
> handle dates when retrieved from a mysql table.
>
> Problem being that converting an input to a date object can be
> unpredictable depending on the type of value thats fed to the date
> type.
>
> Some examples: date(null) date(string) date(0.0) -> 01/01/0001
> 00:00:00 2013-12-18 20:15:57 1970-01-01 01:00:00

Would that not be solved in practice by making certain that the MySQL
field in question is a date field, NOT NULL (and possibly a default
value 0000-00-00 must be specified)?

   (...)
   mydate date NOT NULL default '0000-00-00',

- Jussi

#############################################################
This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Cool date converting

Ke Carlton-3
NULL is a perfectly valid value for a date column.

For example you may have a column called date_deleted or date_closed you
would expect that to be null if the row has never been deleted or closed.
So you must account for these possibilities.

Ke

On 19 December 2013 21:04, Jussi Hirvi <[hidden email]> wrote:

>
> Would that not be solved in practice by making certain that the MySQL
> field in question is a date field, NOT NULL (and possibly a default
> value 0000-00-00 must be specified)?
>
#############################################################
This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Cool date converting

Jussi Hirvi-2
On 19.12.2013 23.24, Ke Carlton wrote:
> NULL is a perfectly valid value for a date column.
>
> For example you may have a column called date_deleted or date_closed you
> would expect that to be null if the row has never been deleted or closed.
> So you must account for these possibilities.

Ok, even if you want to keep NULL as a possibility, you can at least
forget about string and 0.0 as input, at least if you have specified a
default value for the db field.

I always specify NOT NULL for all fields, I think it makes life simpler.

-  Jussi

#############################################################
This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Cool date converting

Jussi Hirvi-2
On 19.12.2013 23.34, Jussi Hirvi wrote:
> Ok, even if you want to keep NULL as a possibility, you can at least
> forget about string and 0.0 as input, at least if you have specified a
> default value for the db field.

I now tested (for the first time ever), and I could not feed to a DATE
field anything that is not in the format 0000-00-00.

My table was:

CREATE TABLE `testtable` (
   `id` int(11) NOT NULL auto_increment,
   `creationdate` datetime NOT NULL default '0000-00-00 00:00:00',
   `moddate` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
   `datetest` date,
    PRIMARY KEY  (`id`)
  );

- Jussi
#############################################################
This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Cool date converting

Jolle Carlestam-2
In reply to this post by Jussi Hirvi-2

19 dec 2013 kl. 22:34 skrev Jussi Hirvi <[hidden email]>:

> On 19.12.2013 23.24, Ke Carlton wrote:
>> NULL is a perfectly valid value for a date column.
>>
>> For example you may have a column called date_deleted or date_closed you
>> would expect that to be null if the row has never been deleted or closed.
>> So you must account for these possibilities.
>
> Ok, even if you want to keep NULL as a possibility, you can at least
> forget about string and 0.0 as input, at least if you have specified a
> default value for the db field.

The suggested method will provide a higher degree of predictability. Since it will treat NULL, empty string and 0 as the same. Not three different outcomes as the date type by itself does. And since there are legitimate and compelling reasons to store date values as integers or decimals, accounting for a 0 or 0.0 value is needed.

> I always specify NOT NULL for all fields, I think it makes life simpler.

Funny, my view is the exact opposite. I always specify default NULL for all fields. To make my life easier. And one thing I'm particularly happy to avoid as often as possible is the 0000-00-00 value that can pop up in a date field if you're not careful.

HDB
Jolle
#############################################################
This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Cool date converting

Brad Lindsay
In reply to this post by Jussi Hirvi-2
The code Jolle shared was *Lasso* code. In Lasso, dates can be created
using strings, integers, or decimals. (The context was just pulling data
from a database.) (Besides, you can store your dates as integers in a
MySql int field if you want.)

Brad


On 12/19/13, 5:03 PM, Jussi Hirvi wrote:

> On 19.12.2013 23.34, Jussi Hirvi wrote:
>> Ok, even if you want to keep NULL as a possibility, you can at least
>> forget about string and 0.0 as input, at least if you have specified a
>> default value for the db field.
>
> I now tested (for the first time ever), and I could not feed to a DATE
> field anything that is not in the format 0000-00-00.
>
> My table was:
>
> CREATE TABLE `testtable` (
> `id` int(11) NOT NULL auto_increment,
> `creationdate` datetime NOT NULL default '0000-00-00 00:00:00',
> `moddate` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
> `datetest` date,
> PRIMARY KEY (`id`)
> );
>
> - Jussi
> #############################################################
> This message is sent to you because you are subscribed to
> the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to <[hidden email]>
#############################################################
This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Cool date converting

Jolle Carlestam-2
In reply to this post by Jussi Hirvi-2
19 dec 2013 kl. 23:03 skrev Jussi Hirvi <[hidden email]>:

> I now tested (for the first time ever), and I could not feed to a DATE
> field anything that is not in the format 0000-00-00.
>
> My table was:
>
> CREATE TABLE `testtable` (
>   `id` int(11) NOT NULL auto_increment,
>   `creationdate` datetime NOT NULL default '0000-00-00 00:00:00',
>   `moddate` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
>   `datetest` date,
>    PRIMARY KEY  (`id`)

`datetest` datetime DEFAULT NULL,

will allow you to send null to the table when adding or updating a row.

HDB
Jolle

Tags: #lasso9 #date #mysql
#############################################################
This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Cool date converting

Jussi Hirvi-2
In reply to this post by Jolle Carlestam-2
On 20.12.2013 0.05, Jolle Carlestam wrote:
> Funny, my view is the exact opposite. I always specify default NULL
> for all fields. To make my life easier. And one thing I'm
> particularly happy to avoid as often as possible is the 0000-00-00
> value that can pop up in a date field if you're not careful.

I guess life stays easy as long as you can predict if you must be
prepared to handle null values or not. I haven't found any use for null,
but I can imagine the opposite.

- Jussi
#############################################################
This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Cool date converting

stevepiercy
On 12/20/13 at 11:26 AM, [hidden email] (Jussi Hirvi) pronounced:

>On 20.12.2013 0.05, Jolle Carlestam wrote:
>>Funny, my view is the exact opposite. I always specify default NULL
>>for all fields. To make my life easier. And one thing I'm
>>particularly happy to avoid as often as possible is the 0000-00-00
>>value that can pop up in a date field if you're not careful.
>
>I guess life stays easy as long as you can predict if you must
>be prepared to handle null values or not. I haven't found any
>use for null, but I can imagine the opposite.

In Lasso 9, this is going to bite you unless you know what has
changed from 8.
http://www.lassosoft.com/Migration-Guide-What-Has-Changed#heading17

To demonstrate, try this in both 8 and 9 and compare the results:

     null == '';
     '\n';
     null == null;

Back to MySQL as a datasource, NULL is simply the absence of a
value.  In the context of a date type, NULL would be interpreted
as "not happened yet, but could be at some point in the future".

For example, let's say you run a soccer camp.  The player
applies for acceptance, the camp reviews the application and
notifies the applicant of acceptance or rejection, and if
accepted then the applicant pays.  That requires three columns:

     date_apply
     date_notify
     date_pay

If you allow NULL on a date type, you can find all those who
have applied but not yet been reviewed and notified.

     select *
     from application
     where date_apply IS NOT NULL
         and date_notify IS NULL

That is so much clearer and more intuitive than the WTF-ness of
this query:

     select *
     from application
     where date_apply != '0000-00-00'
         and date_notify = '0000-00-00'

Another example can be used in scores in a gradebook or surveys
where numbers are chosen.

     "How hairy is your moose?"      0 1 2 3 4 Don't Know
     "How funky is your chicken?"    0 1 2 3 4 Don't Know
     "How loose is your goose?"      0 1 2 3 4 Don't Know

Represented by the fields, all of which allow NULL:

     q_moose
     q_chicken
     q_goose

Whenever someone answers "Don't Know", then its column should be
set to NULL in the database, otherwise its numeric value should
be entered.  Let's say 6 people take the survey, each of them
answering differently for q_goose, and the table has 6 records:

     0
     1
     2
     3
     4
     NULL

What's the average score?  Easy:

     select AVG(q_goose), SUM(q_goose), COUNT(q_goose) from survey
=> 2.0000   5   10

Now if you did not allow NULL, you would get this:

     0
     1
     2
     3
     4
     0
=> 1.6667   6   10

Whoa, not good.  MySQL converts an integer type to 0 by default
if NULL is not allowed, which can totally muck up your faths.

--steve

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- --
Steve Piercy               Web Site Builder              
Soquel, CA
<[hidden email]>                  <http://www.StevePiercy.com/>

#############################################################
This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Cool date converting

Jussi Hirvi-2
On 20.12.2013 12.32, Steve Piercy - Web Site Builder wrote:
> Back to MySQL as a datasource, NULL is simply the absence of a value.
>  In the context of a date type, NULL would be interpreted as "not
> happened yet, but could be at some point in the future".

Ok, in the first example (soccer camp) NULL only made the code a little
more readable. But in the second example, where you had to do math, NULL
was really useful. Good to keep in mind.

- Jussi
#############################################################
This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>