Converting Unix timestamp to actual Date and time

Question

Is there any sql command which I can insert into the stated query so I can convert the timestamp. Although it could be done separately which I have seen so far but I am trying to find something which I can add to the stated query as that would be helpful because I am using other queries to retrieve the data as well. If you any other questions please do mention. Addition: rating_timestamp contains both time and date.

SELECT rating_id,
       rating_postid,
       rating_posttitle,
       rating_rating,
       rating_timestamp,
       rating_username,
       rating_userid
FROM wp_ratings;

http://stackoverflow.com/questions/9251561/convert-timestamp-to-date-in-mysql-query — AJ X., Dec 14 '15 at 19:00
Thanks for the help. I think I am almost there. One thing I failed to mention in the question is this file "rating_timstamp" contains "Date and Time". — Azib.H, Dec 14 '15 at 19:18
I did change the query and right now I am getting the date only. One thing more date is not accurate though. — Azib.H, Dec 14 '15 at 19:19
As you answered `FROM_UNIXTIME`, I guess the question was about MySQL. I fixed the tag to avoid confusion. — Sandra Rossi, Feb 15 '23 at 08:43

score 1 · Answer 1 · answered Dec 14 '15 at 19:09

In cases of date arithmetic, it is especially important to specify the DBMS you are using - Oracle's math is different from Postgres' math is different from SQL Server's math is different from MySQL's math is...

This assumes that you are using SQL Server. Since there is no built in command to do this conversion, you need to create your own function to do that. The function below takes a UNIX / Linux timestamp and converts it to an SQL Server datetime.

CREATE FUNCTION dbo.fn_ConvertToLocalDateTime (@unixdate BIGINT)
RETURNS DATETIME
AS
BEGIN
    DECLARE @UTCTimeOffset BIGINT
           ,@LocalDatetime DATETIME;
    SET @UTCTimeOffset = DATEDIFF(second, GETUTCDATE(), GETDATE())
    SET @LocalDatetime = DATEADD(second, @unixdate + @UTCTimeOffset, CAST('1970-01-01 00:00:00' AS datetime))
    RETURN @LocalDatetime
END;
GO

score 1 · Accepted Answer · answered Dec 16 '15 at 03:51

1

I wast sure about about Sql version before. This worked perfectly for me.

FROM_UNIXTIME(rating_timestamp,'%h:%i:%s %D %M %Y')

answered Dec 16 '15 at 03:51

Azib.H

119
1
2
9

As you say `FROM_UNIXTIME`, I guess it was MySQL. I fixed the question tag to avoid confusion. – Sandra Rossi Feb 15 '23 at 08:42

Converting Unix timestamp to actual Date and time

2 Answers2