Convert Unix Timestamp iinto YYYYMMDD

Question

I have a SQL query which converts a date/timestamp column in my DB from a UNIX timestamp to a readable format. This gives me a nice readable format, but I want to drop the actual timestamp and keep the date only.. As I'm trying to count and group all occurrences of the date only.

SELECT DATEADD(ss,msg.timestamp/1000,'01/01/1970')As DateTime
FROM dbo.cp_messages msg

Result currently looks like this:

2005-10-26 11:12:36.000

But I would like :

2005-10-26

How do I drop the time.

The comments have helped, but how do I search between dates now that I'v converted the unix time into the correct format.. I've tried the below but get errors everytime.

SELECT COUNT(msg.messageId)
,CONVERT(VARCHAR(10), DATEADD(ss, msg.[timestamp]/1000, '01/01/1970'), 120) 
FROM dbo.cp_messages msg
WHERE timestamp >= DATEADD(DAY, -2, GETDATE()) 
GROUP BY timestamp

I seem to end up with a count for every instance, but what I'm after is a total count for that day..

Eg

1   2015-10-29
1   2015-10-29
1   2015-10-29

When I would like

3   2015-10-29

Not sure how to get the desired result

Possible duplicate of [How can I convert bigint (UNIX timestamp) to datetime in SQL Server?](http://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-server) — Ben, Dec 02 '15 at 11:01
In addition to the answers, be careful with date formats when not specifying in YYYY-MM-DD form because 10-01-1970 can be either 1st Oct or 10th Jan! Check out SET DATEFORMAT DMY to force a date format before you import the data. — Tony Rogerson, Dec 02 '15 at 11:01

BeanFrog · Answer 1 · 2015-12-02T11:16:25.733

2

To just display the date you can use this:

select convert(nvarchar(10), [DateTime], 120)

Alternativly, to return the beginning of the day you can use

select dateadd(day, datediff(day, 0, [DateTime]) - 10, 0))

edited Dec 02 '15 at 11:16

answered Dec 02 '15 at 10:56

BeanFrog

2,297
12
26

Now that I've converted mydate to the correct format, How do I look for all records that are several days old.. As I end up get an error when comparing – Boardman411 Dec 03 '15 at 08:23

score 2 · Accepted Answer · answered Dec 02 '15 at 10:58

2

SELECT CONVERT(VARCHAR(10), DATEADD(ss, msg.[timestamp]/1000, '01/01/1970'), 120)
FROM dbo.cp_messages msg

answered Dec 02 '15 at 10:58

Devart

119,203
23
166
186

Brillant that's what I wanted. Could you explain what (Varchar(10) does and the 120 at the end. – Boardman411 Dec 02 '15 at 21:28

BeanFrog · Answer 3 · 2015-12-03T14:34:30.463

1

Second part of the question:

It's giving you a seperate row for each of the 3 rows because you are still grouping on the underlying date. So you get a group for each date with different underlying times. However, you are displaying only the first part of the day, which is why you can't see this.

Instead, group on the start of the day:

SELECT COUNT(msg.messageId) as messageId
      ,CONVERT(VARCHAR(10), dateadd(day, datediff(day, 0, timestamp), 0), 120) as timestamp
FROM dbo.cp_messages msg
WHERE timestamp >= DATEADD(DAY, -2, GETDATE()) 
GROUP BY dateadd(day, datediff(day, 0, timestamp), 0)

edited Dec 03 '15 at 14:34

answered Dec 03 '15 at 10:41

BeanFrog

2,297
12
26

Doesn't seem to work for me... What is the ( -10, 0 ) doing at the end of the line. I get an error, about msg.timestamp being invalid in the select list because its not contained in either an aggregate function or GROUP BY function.. If I add the msg.messageID, I then end up with an Arithmetic o/f – Boardman411 Dec 03 '15 at 14:25
Hmm, not entirely sure where the -10 come from, have removed it. Also updated the select to hopefully remove the error. Sorry about that - can't test it, as you've not provided any sample data for testing! – BeanFrog Dec 03 '15 at 14:36
Arithmetic overflow error converting expression to data type datetime - The data in the columns is just an ID (number) for the messageId and a Unix timestamp for the time. – Boardman411 Dec 04 '15 at 08:48
Try converting timestamp to datetime before you do anything to it then. – BeanFrog Dec 04 '15 at 08:59

score 0 · Answer 4 · answered Apr 19 '20 at 12:29

you can use a map function to convert unix timestamp into actual one like below:

from datetime import datetime
df3['creationdate'] = df3['createDate'].map(lambda d: datetime.fromtimestamp(int(d)))
df3.drop('createDate', axis=1, inplace=True) 
df3.head(7)

you can later confirm if yours newly converted datetime is in actual python date format

Convert Unix Timestamp iinto YYYYMMDD

4 Answers4