Need help Creating A Database that has a time element

Creating A Database that has a time element

[skross]

Hello Everyone,
I have created databases and dynamic webpages that depend on them, but I have never created a database that was concerned with time before.

I now have to create a database that contains contract information that will eventually spit out reports via a webbased frontend based on time periods.

I am unsure how to even start designing this database.

Can anyone give me examples to start with?

I've tried googling examples, but have been frustrated with the results.

Any help would be greatly appreciated.

[Demaestro]

You want to create a DB with a timestamp field.

Something like

create table table_name (

id int primary key not null,
date_created timestamp

)

Then to insert you do this:

insert into table_name values (1, now());

insert into table_name values (2, now());

insert into table_name values (3, now());

That will give you something like this

id | date_added
----------------
1 | 12-02-14 12:03:59
2 | 12-02-14 12:04:11
3 | 12-02-14 12:05:02

[skross]

Oh, I'm sorry but I didn't explain good enough, my apologies.

I've never posted to a forum before. I will try again.

Lets say contract 1 is good for 1/1/2012 to 9/1/2012 and contract 2 is good from 5/1/2012 to 9/1/2012 and contract 3 is good from 5/1/2012 to 1/1/2013 and I'm going to need to be able to pull info from 5/1/2012 to 9/1/2012 on all contracts.

How would I even begin to start? If I can get an example or tutorial that would be awesome

[Demaestro]

hey skross,

No worries, I can help you through it.

From the logic you are describing you want to store 2 date fields to establish a range.

So:

create table contracts (

id int primary key not null,

contract_name text,

contract_start date,

contract_finish date
)

So the data looks like this:

id | contract_name | contract_start | contract_finish
-------------------------------------------------------
1 | abc contract | 1/1/2012 | 9/1/2012
2 | asd contract | 5/1/2012 | 9/1/2012
3 | xyz contract | 5/1/2012 | 1/1/2013


*******************
Now if you want to select contracts that are active you do this:

select * from contracts where contract_start <= CURDATE() and contract_finish >= CURDATE()
*******************


*******************
if you want to select contracts that have expired you do this:

select * from contracts where contract_finish < CURDATE();
*******************

Does that help or is there something else you need the queries to do?

[skross]

so if I wanted to select contracts that are active between 5/1/12 up to 9/1/12 would this work?

select * from contracts where contract_start <= DATE('2012-05-01') and contract_finish >= DATE('2012-09-01')

[Demaestro]

What you are describing could mean 2 things.

1) Do you mean you want contracts that were active for at least one day in that range? EG if contact_finish is 5/2/12 would it be returned because it was active for 2 days during that time period, but not all.

Or

2) Do you mean you want contracts that were active during the entire range given? EG if the contract_start is 2012-05-02 and contract_finish is 2012-08-29

[skross]

My apologies again. I swear I am trying to be clear.

Explaining ideas to others is harder than I first though. Thank you for your patience.

I would want the first option you mentioned for most things I could think of.

Now that you mentioned the second option that could be helpful too.

Would you mind terribly giving me an example of both?

[g1smd]

Make sure you convert all dates/times to a standard time zone such as UTC before storing them.

Although it's February 15th here now (in the UK), for some places it is already February 16th.

The project deadline for 2012-02-18 16:00 Japan Local Time would be stored as 2012-02-18 07:00 UTC.

This allows the US contractor to easily see that he needs to be all done by 2012-02-17 23:00 California Time.

[Demaestro]

Hey skross,

No worries, I have done this kind of thing a couple times and that is the only reason I know about all the little pitfalls.

You will have to do some testing and if it doesn't quite work post back but this is how I think you can pull them both off.

So for #1, which is the contract was active for at least one day in the range given:

select
*
from
contracts
where
(contract_start between DATE('2012-05-01') and DATE('2012-09-01')
Or contract_finish between DATE('2012-05-01') and DATE('2012-09-01'))
OR
(contract_start <= DATE('2012-05-01') and contract_finish >= DATE('2012-09-01'))

//I think that will have it, you have 2 where clauses, the first chacks if the start or the end date is between the range, which would mean it was active at least one day.... then the next one checks to see if the start is equal to or before the date range begins and the end date is equal to or is after the date range ends which would mean the contract was active for the whole time period.


Then for #2, which is the contract was active all days in the range given:

select
*
from
contracts
where
(contract_start <= DATE('2012-05-01') and contract_finish >= DATE('2012-09-01'))

Which checks if the start is before or equal to the date range begins and the end date is after or equal to the date range ends which would mean the contract was active for the whole time period.

I have tested this so what I would do if I were you is create a simple table, populate it with a few rows of different contract_start and contract_finish times and do some testing and make sure it is working the way you need.

I am happy to help if you need more, this should get you most of the way there.... I hope.

[skross]

I will work on this today. Thank you so much!

[Demaestro]

No problem, hope that did it for you.

I meant to say in my last post that I haven't tested it, but I typed that I had...

[skross]

Its working, I really appreciate the help!

[Demaestro]

Nice... no problem, I have been helped in the same way on this forum early on in my career and even recently as well... just paying it forward