MySQL storing unique date in database
Posted: Thu Sep 21, 2006 9:16 am
Hi guys
I'm trying to set up a MySQL database to record USD and EUR exchange rates over time, with the new rates getting entered every month by a user.
There are 6 fields...
id - auto entered
create_stamp - unix timestamp of when the record was created
create_name - username of the user who created the record
exrate_usd - dollar exchange rate
exrate_eur - euro exchange rate
exrate_stamp - unix timestamp of the exchange rate month
What I want to achieve is a way of making sure that people don't enter exchange rates for months that have already been entered.
So if someone's trying to enter the exchange rates for July 06 and there's already a record with a exrate_stamp timestamp of that month, then it doesn't get created.
I had this set up previously with a field which records the month and year in this format 'August 06' then compared it to the month they were entering, and had a Unique index set up on that field in MySQL.
But I was wondering if it's possible to do this using a timestamp instead rather than storing 'August 06' in a VARCHAR field?
Thanks
Ben
I'm trying to set up a MySQL database to record USD and EUR exchange rates over time, with the new rates getting entered every month by a user.
There are 6 fields...
id - auto entered
create_stamp - unix timestamp of when the record was created
create_name - username of the user who created the record
exrate_usd - dollar exchange rate
exrate_eur - euro exchange rate
exrate_stamp - unix timestamp of the exchange rate month
What I want to achieve is a way of making sure that people don't enter exchange rates for months that have already been entered.
So if someone's trying to enter the exchange rates for July 06 and there's already a record with a exrate_stamp timestamp of that month, then it doesn't get created.
I had this set up previously with a field which records the month and year in this format 'August 06' then compared it to the month they were entering, and had a Unique index set up on that field in MySQL.
But I was wondering if it's possible to do this using a timestamp instead rather than storing 'August 06' in a VARCHAR field?
Thanks
Ben