0
452
Question Daniel Buxton · Oct 20, 2020

Convert Epoch Time in MS to smalldatetime to perform COUNT

#Caché #SQL

Hey, 

I have a value in epoch time in which I need convert in order to be able to lookup a table that has dates in YYYY-MM-DD HH:MM:SS format

I thought the below would work but it is not.

select COUNT ('Arrival Time') FROM dbo.table where 'Arrival Time' < DATEADD(ms, CONVERT(int,LEFT(1603173432000, 20)), '1970-01-01 00:00:00')

Discussion (8)0
Log in or sign up to continue

Comments

Julius Kavay · Oct 21, 2020

I'm pretty sure, you intended to write

select COUNT (Arrival Time) FROM dbo.table where Arrival Time < DATEADD(ms, CONVERT(int,LEFT(1603173432000, 20)), '1970-01-01 00:00:00')

i.e. do not place propertynames under quote

By the way, I don't know, how many records you have in your table, but if you have thousands or millions of records, consider to compute constant things just one time!

DATEADD(ms, CONVERT(int,LEFT(1603173432000, 20)), '1970-01-01 00:00:00')

is a constant value, it's not neccessary to compute it for each record! Also, in this particular case, CONVERT() and LEFT() are also not needless, 1603173432000 is the value, and it is an integer.

0
Eduard Lebedyuk  Oct 21, 2020 to Julius Kavay

i.e. do not place propertynames under quote

Property Arrival Time has a whitespace in the name so it must be quoted.

0
Julius Kavay  Oct 21, 2020 to Eduard Lebedyuk

Sorry, I didn't noticed the space char... maybe I need new glasses ;-)

But yes, if you have some unusual property names, then you need double-quotes ($char(34), he used $char(39))

0
Daniel Buxton  Oct 21, 2020 to Julius Kavay

Hey Julius, 

I intended to write 

select COUNT ("Arrival Time") FROM dbo.table where "Arrival Time" < DATEADD(ms, CONVERT(int,LEFT(1603173432000, 20)), '1970-01-01 00:00:00')

However it is returning a count of 0.  1603173432000 being 2020-10-21 13:00:00 so there should be a COUNT of 2

Arrival Time
2020-10-21 12:50:05
2020-10-21 12:57:56
2020-10-21 13:42:11

It is just testing ATM but I dont epect the table to be more than 100 rows as old records get deleted.

0
Julius Kavay  Oct 21, 2020 to Eduard Lebedyuk

Oh yes,  DATEADD(ms, ....) works, but

select  'Arrival Time' ... 

won't work. This gives the string constant of "Arrival Time"

0
Daniel Buxton  Oct 21, 2020 to Eduard Lebedyuk

oh yeah getting closer.  It looks like the webservice I am using is actually sending me UTC Time and I need to convert to my timezone

select DATEADD(ms, CONVERT(string,LEFT(1603274296000+39600000, 20)), '1970-01-01 00:00:00')

adds 11 hours and shows my current time of 2020-10-21 20:58:16

0
Daniel Buxton · Oct 26, 2020

Hey All, 

I will close this post.  Ended tweaking my application so instead of using a webservice to initially get the time and date in epoch, I used select now().  So my application now works as per below, it automatically deals with DST and epoch conversion to the required datetime format.

var1 = select now()

select COUNT ("Arrival Time") FROM dbo.table where "Arrival Time" < var1
0