homepage Welcome to WebmasterWorld Guest from 54.204.215.209
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Website
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Need help Creating A Database that has a time element
Creating A Database that has a time element
skross




msg:4417560
 6:50 pm on Feb 14, 2012 (gmt 0)

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




msg:4417565
 7:04 pm on Feb 14, 2012 (gmt 0)

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




msg:4417569
 7:19 pm on Feb 14, 2012 (gmt 0)

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




msg:4417577
 7:53 pm on Feb 14, 2012 (gmt 0)

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




msg:4417635
 10:09 pm on Feb 14, 2012 (gmt 0)

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




msg:4417652
 10:53 pm on Feb 14, 2012 (gmt 0)

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




msg:4418056
 2:42 pm on Feb 15, 2012 (gmt 0)

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




msg:4418058
 2:48 pm on Feb 15, 2012 (gmt 0)

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




msg:4418078
 4:05 pm on Feb 15, 2012 (gmt 0)

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




msg:4418080
 4:15 pm on Feb 15, 2012 (gmt 0)

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

Demaestro




msg:4418385
 3:54 pm on Feb 16, 2012 (gmt 0)

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




msg:4418481
 8:51 pm on Feb 16, 2012 (gmt 0)

Its working, I really appreciate the help!

Demaestro




msg:4418564
 12:59 am on Feb 17, 2012 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved