Datetime vs Epoche in MySQL

[audiopro]

I am running a MySQL database via PERL and the DBI.
I need to incorporate a number of time and date functions such as 'the time and date a record was last updated' and 'the length of time left before bids close'.
The updated time is easy as it will be the current time, at the time of writing the record. The time remaining will be more dificult as it requires some calculation.
My problem is, not how to do it but which method to use.
The MySQL Datetime field is a convenient way of displaying the data but doesn't seen very calculation friendly whereas the Epoche seconds method is easier to calculate with but needs some formatting to display it correctly.
I am drawn towards the Epoche seconds method but would welcome any advice you may like to offer.

Keith

http://www.studiosoft.co.uk

 

[KevinADC]

If you store the date in machine format like: 1127063764
it will be easier to manipulate for the various tasks you need to do associated with the time stamp. If you store the date in human readable format you might be jumping through hoops later to do time/date calculations.

 

[MikeLacey]

Hi keith

So - what do you get when you read the contents of a DATETIME column using The DBI?

Mike

You cannot really appreciate Dilbert unless you've read it in the
original Klingon.

Want great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[audiopro]

Kevin
My thoughts too - so big numbers it is then.

Mike
Not sure if I understand the mood of the question but the answer is a date time value, which is very pretty much not much use in a calculation.

Keith

http://www.studiosoft.co.uk

 

[regex7]

Hi Keith,

I personally prefer date-time entries in the database, so I understand the entries at first glance. For fast conversion UNIX_TIMESTAMP(date) can be used (see

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html)

to calculate in epoch seconds, but in fact I often calculate dates and times using the Perl Date::Calc module. Makes code more readable and maintanable, I think.

Holger

 

[audiopro]

It is always interesting to hear other people's methods of working. I have gone down the epoch seconds route to save time. Thanks.

Keith

http://www.studiosoft.co.uk

 

[stevexff]

Keith

Databases normally store datetime or timestamp values internally as a 'big number' similar to the epoch, each DBMS using a different method and a different start point. This allows the SQL date time calculation functions to work.

You are correct that the external value (usually something like '2005-01-24-12:30:45.3') is a string literal and not much use in calculation, but if you do the calculation as part of the SQL call, the DBMS will do the work for you.

Think of SQL as another layer of programming you can use - another tool in your box that can do much more than just reading and writing tables. Instead of bringing back a load of data and then performing calculations on it before displaying it, do the calculations in SQL so you bring back the data you actually want in the first place. Sometimes setting up a view in the database can make this a lot simpler. Of course, you have to use your judgement - SQL can be a bit long-winded and clunky for certain types of calculation, but it is quite good at dealing with dates and times.