Skip to main content

Playing with Date and Time in MySQL

Manipulating date and time in database is an important task in any database driven application. MySQL is the most popular open source database deployed widely. Here are some tasks that you can perform on date and time using MySQL built in functions.

Adding interval to a date

DATE_ADD function allows adding/subtracting(using - sign) any interval to date/datetime. The usage of this function is as follows
DATE_ADD(date,INTERVAL [expression] [unit])
unit can be one of the following
MICROSECOND,SECOND,MINUTE,HOUR,DAY,WEEK,MONTH,QUARTER,YEAR,SECOND_MICROSECOND,MINUTE_MICROSECOND,MINUTE_SECOND,HOUR_MICROSECOND,HOUR_SECOND,HOUR_MINUTE,DAY_MICROSECOND,DAY_SECOND,DAY_MINUTE,DAY_HOUR,YEAR_MONTH
expression depends upon the unit. We will explore some units and respected expressions in this post.

Note: All example queries use column name "column_dt" for a column of type datetime.

Adding SECOND(s) to DATETIME
Following query will add 1 second to the value of column_dt
SELECT DATE_ADD(column_dt INTERVAL 1 SECOND)
Adding HOURS:MINUTES:SECONDS and MICROSECONDS to DATETIME
Following query will add 12 hours 40 minutes and 30 seconds to column_dt
SELECT DATE_ADD(column_dt INTERVAL '12:40:30' HOUR_SECOND)
To include microseconds we will use HOUR_MICROSECOND unit like this
SELECT DATE_ADD(column_dt INTERVAL '12:40:30.500' HOUR_MICROSECOND)
Adding DAYS and DAYS+HOURS:MINUTES:SECONDS
Following query will add 2 days to the value of column_dt
SELECT DATE_ADD(column_dt INTERVAL 1 DAY)
To add days along with hours,minutes and seconds we use DAY_SECOND,DAY_MINUTE or DAY_HOUR unit.
Following query will add 2 days , 10 hours 12 minutes and 10 seconds
SELECT DATE_ADD(column_dt INTERVAL '2 10:12:10' DAY_SECOND)
Following query will add 3 days 10 hours and 12 minutes
SELECT DATE_ADD(column_dt INTERVAL '3 10:12' DAY_MINUTE)
Following query will add 3 days and 2 hours
SELECT DATE_ADD(column_dt INTERVAL '3 10' DAY_HOUR)
For complete reference of units and expressions for DATE_ADD please refer to this link.

Extracting parts from DATETIME

DATE function extracts only date part from DATETIME type
SELECT DATE('2014-01-01 12:33:40') will return '2014-01-01'
To extract other parts from datetime like hour, minute , second etc there is a function EXTRACT. It syntax is as follows
EXTRACT(unit FROM datetime)
Unit in EXTRACT are same as unit in DATE_ADD
  • SELECT EXTRACT (YEAR_MONTH FROM '2014-01-11') will return 201401
  • SELECT EXTRACT(DAY_MINUTE FROM '2014-01-11 01:02:03') will return 20102 
  • SELECT EXTRACT(YEAR FROM '2014-01-11') will return 2014

Getting variants of DAY from DATETIME

SELECT DAYNAME('2014-05-15') returns Thursday 
SELECT DAYOFMONTH('2014-05-15') returns 15 
SELECT DAYOFWEEK('2014-05-15') returns 5 (1=Sunday,2=Monday,3=Tuesday...) 
SELECT DAYOFYEAR('2007-02-03') returns 34 (DAYOFYEAR returns day in the year values between 1 - 366)

Converting DATETIME between two TIMEZONES 

CONVERT_TZ(dt,from_tz,to_tz) function converts the passed dt parameter into to_tz timezone from from_tz . Timezones can be passed in two forms

CONVERT_TZ('2014-05-15 12:00:00','GMT','MET') will return
'2014-05-15 13:00:00'
CONVERT_TZ('2014-05-15 05:00:00','+00:00','+05:00') will return
'2014-05-15 10:00:00'


Labels:

Comments

Post a Comment

Share your wisdom

Popular posts from this blog

Decoding JPEG image file using libavcodec

I got a chance to work on a video encoding application that decodes series of jpeg files and convert them into ogg theora video file. I used the infamous libavcodec library that is used in FFMPEG . I decided to write blog posts explaining how I decode jpeg images and convert them into ogg video file. This is the first part and in this I will explain how to decode jpeg images using libavcodec. To learn how to write decoded images as a ogg video file please read http://random-stuff-mine.blogspot.com/2017/07/encoding-raw-images-to-ogg-theora-video.html Before reading this blog post you must be aware of using and setting up libavcodec. I highly recommend this tutorial to get basics of using libavcodec http://www.ffmpeg.org/doxygen/0.6/api-example_8c-source.html Allocating input format context We will first allocate input format for reading the file. We will use avformat_open_input function that will allocate AVFormatContext structure passed to it , the function detects input typ...
Post a Comment
Read more

Multithreaded C# TCP server to handle multiple clients

I decided to write a minimal multithreaded TCP based server as a blog post. Following class can serve as a skeleton for any small or large scale multithreaded TCP socket server. It do not contain much error handling , it is only to give an idea that how multithreaded server works and how it can process multiple clients using threading. using System; using System.Text; using System.Net; using System.Net.Sockets; using System.Threading; using System.Collections.Generic; namespace RandomStuffMine { public class MTServer {     public int Port{get;set;}     public Socket ServerSocket{get;set;}     private List<Client> Clients=new List<Client>();     private bool runServer=true;     public MTServer(int port)     {         Port=port;         ServerSocket = new Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp);     }   ...
1 comment
Read more

CryptographicException: An error occurred while trying to encrypt the provided data. Refer to the inner exception for more information

I created a new Blazor Server app in Visual Studio 2019 and tried to run it. But I was getting this error CryptographicException: An error occurred while trying to encrypt the provided data. Refer to the inner exception for more information. I couldn't find any reason or solution to this problem. I tried creating the project multiple times but same error. I created a new .Net Core Web App and added a new razor component and included that component in a razor page (cshtml file) like this @(await Html.RenderComponentAsync<GeofenceWork>(RenderMode.ServerPrerendered)) and <component type="typeof(GeofenceWork)" render-mode="serverprerendered" /> As soon as I navigate to this page that has component added I got the same error: CryptographicException: An error occurred while trying to encrypt the provided data. Refer to the inner exception for more information. This was very frustrating. After hours of trying and searching I figured out the solution.  ...
Post a Comment
Read more