Click here to Skip to main content
15,444,523 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
The main question is

What is best filed type that i can use in table “product_price” ?

Datetime

Timestamp

Or ...

And which format is best for it ?

I have 3 table

1 ) group -----> (group_id,group_name)

2 ) product --->(group_id,product_id,product_name)

3 ) product_price ----> (product_id,price_id,price_value,price_date)


Note : maybe in one day multi records saved . so time must save in date format ,too.

What is best filed(s) for Date in table “product_price” ?

I want to display any charts based on date from my database

(for example see http://www.kitco.com/charts/livegold.html[^])

I want that i can get different between first and last record(based on date field) from table “product_price” And then generate available date that user can see chart .

Example1)

Suppose different between first and last record is 35 days , in this case available chart is :

Today

Yesterday

Last week

Last month


Note : Last 3 month not available

Example2 )

Suppose different between first and last record is 253 days , in this case available chart is :

Today

Yesterday

Last week

Last month

Last 3 month

Last 6 month


Note : Last year not available

Example3 )

Suppose different between first and last record is 900 days , in this case available chart is :

Today

Yesterday

Last week

Last month

Last 3 month

Last 6 month

Last year

Last 2 year


Note : Last 3 year not available

And Finaly I say Again : The main question

What is best filed type that i can use in table “product_price” ?

Datetime

Timestamp

Or ...

And which format is best for it ?
Posted
Updated 10-Feb-14 2:24am
v2
Comments
EZW 10-Feb-14 17:21pm    
I use an integer(11) for date and put in a time value ($time = time();) in there.
Killzone DeathMan 11-Feb-14 9:02am     CRLF
Thats good :) I use DATETIME format, "2014-02-11 14:02:13"

1 solution

SQL
The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

...

The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. It has varying properties, depending on the MySQL version and the SQL mode the server is running in.




Timestamps in MySQL generally used to track changes to records, and are often updated every time the record is changed mention that you have to design field as not null. If you want to store a specific value you should use a datetime field.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900