FileTime to DateTime and Back in MySQL

Introduction

Sometimes, when we are using MySQL or MariaDB for our projects in Windows, there might be a need to convert FileTime values to DateTime and vice versa. There are two user functions that will allow us to make the conversion "on the fly" without sending the data to external tools or code.

Background

The provided code will create two user functions when executed.

Using the Code

The function FTime2DTime will convert FileTime value to DateTime value, and DTime2FTime will convert DateTime value to FileTime value.

DELIMITER $$

USE `<Your database name>`$$

DROP FUNCTION IF EXISTS `FTime2DTime`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `FTime2DTime`(Ftime BIGINT) RETURNS VARCHAR(40) CHARSET utf8
    DETERMINISTIC
BEGIN
    DECLARE Result VARCHAR(40);
    SET Result = DATE_ADD('1601-01-01',INTERVAL (Ftime/10) MICROSECOND);
    RETURN (Result);
END$$

DROP FUNCTION IF EXISTS `DTime2FTime`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `DTime2FTime`(DTime DATETIME) RETURNS BIGINT
    DETERMINISTIC
BEGIN
    DECLARE Result BIGINT;
    DECLARE MsecBetween1601And1970 BIGINT;
    DECLARE MsecBetween1970AndDate BIGINT;    
    
    SET MsecBetween1601And1970 = 11644473600000;
    
    SET MsecBetween1970AndDate = TIMESTAMPDIFF(SECOND,'1970-01-01 00:00:00',DTime)* 1000; 
    
    SET Result = (MsecBetween1601And1970 + MsecBetween1970AndDate) * 10000;
    
    RETURN (Result);
END$$

DELIMITER ;

Usage example:

SELECT
  `timestamp`,
   DTime2FTime(`timestamp`) TimestampConvertedToFileTime
FROM `<your database>`.`<your table>`
LIMIT 0, 1000;

Happy coding!