![]() ![]() ![]() As an aside, a timestamp is not stored as a string, it's stored as an 8-byte integer, exactly the same as bigint: PostgreSQL documentation. The data type of a timestamp is: timestamp. You can use DATE wherever you just need to keep track of the date and exact time is unnecessary while you can use timestamp where knowing the time is crucial. You should always store data in its native data type so you can use the built-in functions. ConclusionĭATE and timestamp have one main difference and that is that the DATE gives you only current date while timestamp gives you date in addition to the time ( with the option of time zones as well ). Both ‘timestamp’ and ‘timestamptz’ have min value of 4713 BC, a max value of 294276 AD, and a resolution of 1 microsecond/ 14 digits. If we talk about storage then both of these timestamp related data types would take 8 bytes each when they will be used. Whenever you need to know ‘timestamptz’ from database, PostgreSQL converts the UTC saved time back to the local time zone and displays it to you. Essentially, ‘timestamptz’ doesn’t give you time in UTC by default but rather PostgreSQL converts it for you and then save it in database. PostgreSQL will never save time directly in exact ‘timetamptz’ mode. Whenever you use this data type, PostgreSQL stores the values in table in Coordinated Universal Time (UTC) format. The basic difference between both of them is that the former gives you time without time zone while latter gives you time with time zone. PostgreSQL provides two timestamp related datatypes ‘timestamp’ and ‘timestamptz’. You will, of course, always have to be concerned that the current session time zone is set properly whenever you are using this type with date functions such as NOW().The default format in both cases would be yyyy-mm-dd and if you want you can change that to any format you like. In that case you will have to use a DATETIME type. The TIMESTAMP type has a range of ' 00:00:01' UTC to ' 03:14:07' UTC and so it may not usable for your particular application. ![]() If I did not need to do time comparisons, then I would probably be better off just using a DATETIME column, which will display correctly (with an implied EST time zone) regardless of what the current session time zone is. Of course, for displaying in a meaningful way to a perspective customer, I would need to set the correct session time zone. If I want to know whether the event has occurred or not, regardless of the current session time zone setting I can compare the start time with NOW(). If my server is in San Francisco but I am running an event in New York that starts on at 20:00, I would use a TIMESTAMP column for holding the start time, set the session time zone to 'America/New York' and set the start time to ' 20:00:00'. It's a useful fiction to think of a TIMESTAMP as taking the value you are setting and converting it from the current session time zone to UTC for storing and then converting it back to the current session time zone for displaying. When the column is displayed, it will be converted back for display based on whatever the current session time zone is. But once a DATETIME column has been set, it will display the same regardless of what the current session time zone is.Ī TIMESTAMP column on the other hand takes the ' 12:15:00' value you are setting into it and interprets it in the current session time zone to compute an internal representation relative to 00:00:00 UTC. The value stored will be the current date and time using the current session time zone in effect. Was this Eastern Standard Time? Pacific Standard Time? Who knows? Where the current session time zone of the server comes into play occurs when you set a DATETIME column to some value such as NOW(). ![]() So, I can set a DATETIME column to a value such as ' 12:15:00' to indicate precisely when my last birthday occurred. A DATETIME stores a literal value of a date and time with no reference to any particular timezone. I have a slightly different perspective on the difference between a DATETIME and a TIMESTAMP. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |