Timestampt as a primary key in MySQL
denis sorn
Ranch Hand
Posts: 33
posted 8 years ago
Hi!
I am working on app which should work somthing like a gas storage optimizer, one day hopefully. Now I am trying to implement a storage as a MySQL table with timestamp as a primary key. So I should be able to refer any hour in next three years for example, and to get available capacity of storage, injection and withdrawal rate for it.
Now my problem is that that MySQL converts timestamps to local time of server and I am getting two same timestamps in Oct when changing from summer time to winter.
I am thinking about to use double instead of timestamp and to cast java.sqlTimestampt to double before putting it in the database. In this case I have additional work to do in java because I have to track last sundays of March and October to substract/add values (inj/withdrawal rate, gas) for an additional/missing hour each time.
Any help/suggestion on this : S?
I am working on app which should work somthing like a gas storage optimizer, one day hopefully. Now I am trying to implement a storage as a MySQL table with timestamp as a primary key. So I should be able to refer any hour in next three years for example, and to get available capacity of storage, injection and withdrawal rate for it.
Now my problem is that that MySQL converts timestamps to local time of server and I am getting two same timestamps in Oct when changing from summer time to winter.
I am thinking about to use double instead of timestamp and to cast java.sqlTimestampt to double before putting it in the database. In this case I have additional work to do in java because I have to track last sundays of March and October to substract/add values (inj/withdrawal rate, gas) for an additional/missing hour each time.
Any help/suggestion on this : S?
posted 8 years ago
First of all you want to use Timestamps as the primary key in your table, and then you want to run logic and conversions against it. I can't help thinking "no, no, please no".
Firstly, Timestamps (and other date/time types) are more complicated than regular numbers. You mention this in your own posts, but I bring it up because this difference can have a direct effect on your table if you key it on a Timestamp. Based on anecdotal evidence only, I believe it cause you real problems in the future.
The second problem, which you also mention, is that the conversions can cause collisions, which is about as bad a problem as you can get with primary keys.
I recommend using a regular number as the primary key, and then working out what 'views' you need on the Timestamp and possibly denormalising this field. You can store the 'milliseconds' as a long, but consider separately storing the month, day, day of week, week of year etc as denormalised (figured) data to reduce the effort required for the database to maintain consistent data. You should also index the heavily used fields.
Firstly, Timestamps (and other date/time types) are more complicated than regular numbers. You mention this in your own posts, but I bring it up because this difference can have a direct effect on your table if you key it on a Timestamp. Based on anecdotal evidence only, I believe it cause you real problems in the future.
The second problem, which you also mention, is that the conversions can cause collisions, which is about as bad a problem as you can get with primary keys.
I recommend using a regular number as the primary key, and then working out what 'views' you need on the Timestamp and possibly denormalising this field. You can store the 'milliseconds' as a long, but consider separately storing the month, day, day of week, week of year etc as denormalised (figured) data to reduce the effort required for the database to maintain consistent data. You should also index the heavily used fields.
denis sorn
Ranch Hand
Posts: 33
posted 8 years ago
Thanks for the reply. Well I am not sure what did you mean with 'First of all you want to use Timestamps as the primary key in your table, and then you want to run logic and conversions against it.'
Actually I would like to use it without conversions. My Problem is that MySQL server does conversions automaticaly to adjust summer time - winter time transitions. Thats's why I get two same timestamps per year.
EDIT:
Sorry
I got it. You have simply meant neither using timestamp as primary key nor converting it to milisec is smart solution.
[ August 06, 2008: Message edited by: denis sorn ]
Actually I would like to use it without conversions. My Problem is that MySQL server does conversions automaticaly to adjust summer time - winter time transitions. Thats's why I get two same timestamps per year.
EDIT:
Sorry
I got it. You have simply meant neither using timestamp as primary key nor converting it to milisec is smart solution.[ August 06, 2008: Message edited by: denis sorn ]
