Tag: Time
MySQL Date Format: What Datatype Should You Use? We Compare Datetime, Timestamp and INT.
Whenever you need to save datetime data, a question arises about what MySQL type to use. Do you go with a native MySQL DATE type or use an INT field to store date and time info as a plain number?
In this article, I’ll explain MySQL’s native options and give you a comparison table of the most common datatypes. We’ll also benchmark some typical queries and reach some conclusions about which datatype to use in a given situation.
SQL Server 2008 Datatypes and the Death of Datetime
Beginning with SQL Server 2008, the datatypes which store date and time were greatly improved. Despite this, many legacy databases (and often newly developed ones) still use Datetime and SmallDatetime, the original datatypes. Aside from the dwindling possibility of backwards compatibility issues, there is no advantage to using these old datatypes; as this article will show, the new implementations are superior in every way.
SQL 2005 and Earlier: Datetime and SmallDatetime Datetime and SmallDatetime are the original temporal SQL Server datatypes, and the only options available in SQL Server 2005 and earlier.
Time Zones in Databases
Anyone who had to schedule an intercontinental phone call knows that there is no such thing as a simple time called now. What you should rather think about is a time comprised of here and now.
The Earth rotates around its own axis. When it’s solar noon (the sun is at its highest position) in one place, it’s already past noon in places to the east and it’s still before noon in places to the west.
Date Arithmetic in PostgreSQL
If your default programming language, like mine, is Java, you most likely wince at the very thought of date arithmetic. (It’s changed for the better with Java 8 but Vertabelo is not there yet.)
The date arithmetic API in PostgreSQL is like a breath of fresh air.
Interval Arithmetic You can represent a time interval:
select interval '2 days'; '2 days'
select interval '3 hours'; 03:00:00