what's the best way to store information about timezone in mysql database? I don't have any information about a time, only timezone, for instance: +02.00, -03.00 etc. Do you think that varchar (6) will be correct?
Asked
Active
Viewed 6,989 times
2
-
thanks, I will read this. I'm sorry that I didn't find it earlier. – Marcin Erbel Jun 12 '13 at 11:37
-
1You don't have a *time zone*. You have a *time zone offset*. An offset is usually not useful in isolation. Please read "Time Zone != Offset" in the [timezone tag wiki](http://stackoverflow.com/tags/timezone/info). – Matt Johnson-Pint Jun 12 '13 at 15:40
2 Answers
5
VARCHAR(6) is fine, but personally, i would store the offset using a signed SMALLINT (in minutes).
Like this, you can store numbers from -32768 to 32767.
You could also store it as TINYINT if you just want to store the hours-offset. (from -128 to 127)
Stefan
- 2,028
- 2
- 36
- 53
1
its all depends upon on you, but Varchar works for you.
decimal(2,2)
also works fine for storing time zone. For other Date related data types are as follows
Data Type
1-time
2-timestamp
3-date
4-datetime
KhAn SaAb
- 5,248
- 5
- 31
- 52
-
decimal is not optimal, because it would allow you to store for example `2.9` which you won't see as a time offset. – Stefan Jun 12 '13 at 09:44
-
-
-
-
@steve it depends on you, its not a mysql responsibility to take care of these issues, i am only suggesting him to stor time zone kind of values. – KhAn SaAb Jun 12 '13 at 10:04
-
I think it's the best way to store information, cause we can use it in queries easier than varchar type – Marcin Erbel Jun 14 '13 at 15:23