When dealing with applications that span multiple time zones, you’ll often want to store timestamps in UTC. That’s usually the best practice – it avoids confusion and ensures consistency. But sooner or later you’ll need to show users dates and times in their own local time zones. There are a few ways to handle this in SQL Server.
time format
How to Handle Server vs Session Time Zone Settings in SQL Server
Working with dates and times in SQL Server can get tricky, especially once you add time zones into the mix. One common point of confusion is the difference between the server’s time zone and the session’s time zone. If you’re not careful, you can end up with inconsistent data or timestamps that don’t line up with what your users expect.
Let’s look at how SQL Server treats time zones and how you can handle server versus session time zone differences cleanly.
Add a Time Zone Offset to a datetime2 Value in SQL Server (T-SQL)
In SQL Server, the TODATETIMEOFFSET() function was specifically designed to return a datetimeoffset value from a datetime2 value.
Given the fact that the datetime2 data type doesn’t actually support time zone offsets, and datetimeoffset must contain the offset, the TODATETIMEOFFSET() function allows you to specify a time zone offset to use.
This article provides some examples to demonstrate.
4 Ways to Separate Hours, Minutes, and Seconds from a Time Value in MariaDB
MariaDB has several functions that enable you to extract various date and time parts from date/time values. You can use these to separate each date/time component into its own column if required.
Below are four ways to extract the hours, minutes, and seconds from a time value in MariaDB. As a bonus, we’ll also include microseconds.
MariaDB Composite Date & Time Units Explained
MariaDB includes a bunch of date and time units that you can use when working with date and time values. For example, MONTH is a unit, and HOUR is another unit.
Some units are composite units. Composite units are when two units get combined into one. The naming convention is that each unit name is separated by an underscore. For example, MINUTE_SECOND is for minutes and seconds.
Below are some examples that demonstrate how composite units work in MariaDB.
4 Functions to Get the Hour from a Time Value in MariaDB
Below are 4 functions that enable you to return the hour from a time value in MariaDB.
4 Functions that Return the Minutes from a Time Value in MariaDB
Below are 4 functions that enable you to return the minutes portion from a time value in MariaDB.
How to Add AM/PM to a Time or Datetime Value in MariaDB
MariaDB includes many functions that allow you to return time and datetime values in a variety of formats.
Two functions allow you to format the time portion using a 12 hour clock, with the AM/PM designator are DATE_FORMAT() and TIME_FORMAT().
There are many format specifiers that can be used with these functions, but only a couple that return the AM/PM designator.
4 Functions that Return the Seconds Part from a Time Value in MariaDB
Below are 4 functions that enable you to return the seconds part from a time value in MariaDB.
HOUR() vs EXTRACT(HOUR …) in MariaDB: What’s the Difference?
MariaDB has an HOUR() function that extracts the hour portion from a time value. MariaDB also has an EXTRACT() function that can also extract the hour portion from a time value.
However, these functions don’t always return the same result.
Read on to see the difference between HOUR() and EXTRACT(HOUR FROM ...) in MariaDB.