EasyCFM.COM ColdFusion Forums / MySQL Related Issues / MySQL timestamp dilemna - do or do not?

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: MySQL timestamp dilemna - do or do not?

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

Wizzar
03-06-2008 @ 1:30 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 159
Joined: Sep 2003

HI all,

I have a question concerning MySQL (4.01) and Cold Fusion 7.0. This is in regard to date logic and speed of processing.

Ok, I have been working on a subscription system (typical login stuff). Now, according to typical Cold fusion the following statement is logical and considered to be standard notation..


<CFSET today_date = Now()> <!--- this will of course, render the following in the date field...


{ts '2008-03-06 09:59:03'}

This is your classic timedate stamp.



However, I am wondering if it advantageous to setup a custom datestamp, as In my case, my project (subscription project) does not rely on PRECISE date time combinations. My project only requires DAY / DATE logic and no hourly, or seconds formatting.

Can I get away with setting the formatted date fields with the following?

CFCODE:

<CFSET today_date = Now()>
<CFSET today_normal = DateFormat(today_date,"dd-mm-yy")>


- which will render the following:

06-03-08


Is this formatting acceptable to MySQL through CF 7.0? Is there any speed or performance trade offs?

Anyone with experience in this issue, Please give me the benefit of your experience!!

Thank you in Advance.





Rooting in work is crucial to any accomplishment. Rooting in mere enthusiasm will in the long run force illusory measures to keep the fires of empty enthusiasm going. And this makes politics and politicians.
Wilhelm Reich -- April 19, 1951

SirRawlins
03-06-2008 @ 1:45 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 951
Joined: Mar 2006

Hey Wizzar,

My advice would probably be to leave it as it is. There isnt any negative aspect to saving a full date/time stamp into the database, this is how it 'should' be done. If you dont need the full date/time for your application or you want to reformat it then simply do it when you output the date, rather than before it goes in, like so:

<cfoutput>#dateFormat(MyQuery.MyDate#, "dd-mm-yyyy")</cfoutput>

When data is placed into the database I always think it is best to keep it as raw as possible, all the formatting and manipulation should be done when you pull it back out, with dates in particular I can think of a few reasons.

A) The database loves to store the full date/time, its what it was built to do, if you change that then the poor database wont ever fullfill its destiny, and being a MySQL database it has a hard enough time as it is :-D

B) You never know when the application spec might change, all of a sudden a new business requirement comes into view which means you need to know the exact times your data is changing, only problem being you've got 500,000 rows of old data which doesnt have a time stamp on it because you cut it off before entering it into the database.

C) Localization, you might find yourself moving or selling the application into non english speaking countries, in which they format thier dates and times very differently, if you've cropped it and chopped it all around then that wont work.

At the end of the day, its always a 'best practice' to store the date in the database in as full state as possible, if you really think that the time isnt ever going to be applicable then by all means that can be lost, but to be honest, I dont see the point, there is no performance detrement or storafe space consumption benefits to cropping it, so leave it be.

Just my thoughts,

Rob

Wizzar
03-06-2008 @ 2:00 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 159
Joined: Sep 2003

Fair enough SirRawlins!

Your arguements make good sense. I'm glad I consulted someone about this, because there is a chance that the app will be resold in the future (better safe than sorry). I'm not a gambler Smile

There could be a chance that there is a hidden wisdom to saving the Timestamp "RAW" as you say...and I'm going to follow your suggestion on this!


Thanks mate!


Rooting in work is crucial to any accomplishment. Rooting in mere enthusiasm will in the long run force illusory measures to keep the fires of empty enthusiasm going. And this makes politics and politicians.
Wilhelm Reich -- April 19, 1951

Lossed
03-06-2008 @ 3:11 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

Hi Wizzar,

As per Rob's suggestions, raw is good. It gives you more options later without any significant cost. Take, as a very crude example, you want to send those new subscribers an activation email with a link they have to click to activate their membership. Say you want to put a time limit of 5 hours from when they signed up. You'd never be able to check that if there was only a date value stored. The best you could do would be next day.

Also, there may be no merit in assigning a var to now() in CF and inserting that into the DB. Just let MySQL handle it with it's own now() function into a datetime column, or take a look at the timestamp data type and whether, prior to 4.1, you can dictate whether it is only set on insert,not on updates. With the timestamp data type, you won't even need to use now() in the query, MySQL will automatically initialise the value with now() when you insert a fresh entry. Just be sure, if using timestamp, you are able to set it so the value doesn't change on updates, so you will only ever have the d/t the user signed up, rather than when they last updated their details, if that's what you want.

Lossed
---------------------------
When the only tool you have is a hammer, everything looks like a nail Smile
-----------------------------

Lossed
03-06-2008 @ 3:21 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

I thought there might be a change at 4.1 and there was. Prior to 4.1, if you use a timestamp data type and don't want the value to change on updates, then when you do your updates, you need to explicitly set the timstamp value to it's current value (this tells MySQL to ignore it):

update myTable
set
    blah='blah',
    myTSColumn=myTSColumn

reference is here

Lossed
---------------------------
When the only tool you have is a hammer, everything looks like a nail Smile
-----------------------------

louissto56
03-08-2008 @ 8:38 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1149
Joined: Jan 2007

Ok its late so give me a break if I'm goin off topic...

Generally I do the above. Make a datetime field for everything just in case. But when updating/inserting times cfqueryparam is the king for doing that:

myTime = <cfqueryparam value="#now()#" cfsqltype="cf_sql_timestamp">

Au Revoir

Louis

-----------------------------
www.MyBizList.com.au - QLD Business Directory and rent-a-site

Lossed
03-09-2008 @ 12:15 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

L-Dawg,

I could be wrong, but I think support for prepared statements and bind vars was introduced in version 4.1. Wizzar's version probably doesn't support such, so the data binding aspect of cfqueryparam is probably not gonna happen.

That leaves the security benefit of cfqueryparam, but I can't see any injection risk when using an in-built MySQl function like cur_date() or now(), etc.

So I'm not sure whether there can be any benefit to using cfqueryparam in this instance?

Chur.

Lossed
---------------------------
When the only tool you have is a hammer, everything looks like a nail Smile
-----------------------------

louissto56
03-09-2008 @ 1:35 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1149
Joined: Jan 2007

Well doesnt cfqueryparam format the date and time in a database friendly way? I don't exactly use it for security reasons.

But yeah version 4 may not have data binding. Google anyone?

Louis

-----------------------------
www.MyBizList.com.au - QLD Business Directory and rent-a-site

Lossed
03-09-2008 @ 1:52 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

It probably doesn't get any friendlier than the MySQL now() function?

Lossed
---------------------------
When the only tool you have is a hammer, everything looks like a nail Smile
-----------------------------

louissto56
03-09-2008 @ 8:06 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1149
Joined: Jan 2007

Your damn right it doesnt. But usually my dates are returned from a database not actually now().

Louis

-----------------------------
www.MyBizList.com.au - QLD Business Directory and rent-a-site


Website Designed and Developed by Pablo Varando.