record loading

Question

record loading

Viewing 15 posts - 31 through 45 (of 55 total)

You must be logged in to reply to this topic. Login to reply

Bruin SSCrazy Points: 2091 More actions · Answer 1

I did this for both 12-21 and 12-26 .. then inserted into open query should this get me all records in that range.

Thanks for help..

SELECT DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), '12-21-2021');


--



DECLARE @GivenDateTime DATEtime = '2021-12-21 05:00:00.000'

;

SELECT OldUnixTS=DATEDIFF (ss,'1970',@GivenDateTime) --Always based on Seconds

,NewUnixTS=DATEDIFF_BIG(ms,'1970',@GivenDateTime) --Based on Milliseconds, in this case.

;


--



select * 
from OPENQUERY(jcsql, 'select tagid,floatvalue,dateadd(s, convert(bigint, t_stamp) / 1000, convert(datetime, ''1-1-1970 00:00:00'')) from ignition.dbo.sqlt_data_1_2021_12
where( t_stamp >= ''1640062800000'' and t_stamp <= ''1640408400000'') and floatvalue is not null ')

Jeffrey Williams SSC Guru Points: 90313 More actions · Answer 2

You seem to continue mixing/matching different data types and expecting correct values. Is the column t_stamp the old Unix timestamp based only on seconds - or is it the new style based on milliseconds?

In your open query - you are passing the millisecond value in the where clause, but then attempting to convert the t_stamp using seconds. Which one is it?

If you want all data through the end of 2021-12-25 but are only using the equivalent of midnight on that day, will it return everything you expect? Or rather - did you mean you want everything up to but not including 2021-12-25? As you have it now it will include 12/25 at midnight only.

Like I said before - I would use AT TIME ZONE to calculate UTC, especially since that function will consider DST. So: CAST('2021-12-21' AS datetime) AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'UTC' returns '2021-12-21 05:00:00.000 +00:00'.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 3

Jeffrey Williams wrote:

I am not even sure what your function is trying to do here - or why you would even care about UTC. The UNIX timestamp is an offset in seconds or milliseconds from a base date and converting to/from the UNIX timestamp really doesn't have anything to do with UTC.

Just to be sure, UNIX timestamps are, by definition, based on UTC. See the first paragraph of the definition at the following link...

https://en.wikipedia.org/wiki/Unix_time#Definition

... which states ...

Two layers of encoding make up Unix time. The first layer encodes a point in time as a scalar real number which represents the number of seconds that have passed since 00:00:00 UTC on Thursday, 1 January 1970.[8] The second layer encodes that number as a sequence of bits or decimal digits.

Of course, that's referring only to the old UNIX timestamps that were only based on seconds. Some are based on milliseconds (which I believe Bruin is having to deal with because of the "/1000" in his code and is a BAD way to do it, as you know and I'm saying just for other readers), some are based on microseconds, and I've seen some based on nanoseconds. Just substitute whichever fractional level you want to replace "seconds" and the definition stays the same.

In other words, done properly, UNIX is based on UTC (although not 100% because it doesn't account for leap seconds).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90313 More actions · Answer 4

I understand that, but converting to or from a Unix timestamp doesn't need to know anything about UTC. The conversion is strictly based on the number of seconds or milliseconds from the base date.

What you do before or after conversion may be important, depending on the desired outcome.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 5

"It Depends" on what you're talking about. If you're on Eastern Time and you want the correct data based on a UNIX timestamp, then you need to convert your local datetime to UTC and then do the conversion in order to get the correct data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90313 More actions · Answer 6

Correct, you convert the local time to UTC then perform the conversion to Unix timestamp.

In fact, the numeric timestamp is only UTC by convention. There is nothing inherent in that number that makes it a UTC date and time. It is just a number that defines a point in time.

We could say the same thing about datetime in SQL Server. We could say that the zero time for datetime is 19000101 at midnight UTC. Which is exactly what happens when you store datetime as the UTC date and time.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Manish_Bose Grasshopper Points: 11 More actions · Answer 7

Have a doubt regarding converting long data to wide data in SQL with out using hard coding?

Regards

Manish Bose

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 8

Manish_Bose wrote:

Have a doubt regarding converting long data to wide data in SQL with out using hard coding?
Regards
Manish Bose

You should open your own post, Manish, so that it doesn't "get lost" on this thread. And have a look at the article at the first link in my signature below. People will jump through hoops with you if you post a properly written question.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 9

Jeffrey Williams wrote:

Correct, you convert the local time to UTC then perform the conversion to Unix timestamp.
In fact, the numeric timestamp is only UTC by convention. There is nothing inherent in that number that makes it a UTC date and time. It is just a number that defines a point in time.
We could say the same thing about datetime in SQL Server. We could say that the zero time for datetime is 19000101 at midnight UTC. Which is exactly what happens when you store datetime as the UTC date and time.

Sounds like we're on the same page. Thanks, Jeffrey.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90313 More actions · Answer 10

Thanks, I do believe we are.

This reply was modified 2 years ago by Jeffrey Williams. Reason: Stupid phone

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Bruin SSCrazy Points: 2091 More actions · Answer 11

I ended up with this as my query...

Would there be any way to get the range of t_stamp values for 01-21-2022 thru 01-25-2022 and send that into query string rather than doing the date convert?

So midnight 01-21-2022 00:00:00 thru 01-25-2022 23:59:59 how can I get that starting number(t_stamp) and ending(t_stamp) and pass to query.

example:

starting 15250

ending 18200

Then I could replace the convert date piece and use (T-stamp >= xxx and T-stamp <= xxx)

If so, can you send example ..

Thanks.

select * 
from OPENQUERY(xxxx, 'select tagid,floatvalue,t_stamp from ignition.dbo.sqlt_data_1_2022_01
where 
(tagid not like ''7%'' and floatvalue is not null)
and
convert(date,DATEADD(s,t_stamp/1000,''1969-12-31 20:00:00'')) >= ''01-21-2022'' and convert(date,DATEADD(s,t_stamp/1000,''1969-12-31 20:00:00'')) <= ''01-25-2022''');

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 12

You're still using this junk...

convert(date,DATEADD(s,t_stamp/1000,''1969-12-31 20:00:00'')

There's no need to convert the time stamps on the remote server to date time. You also already know how to convert local dates to time stamps to make the comparison. An using any form of 23:59:59 is wrong and prone to resolution changes. Always use < "the next day" in your temporal comparisons.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCrazy Points: 2091 More actions · Answer 13

If I want to do between 2 dates example:

2/11/2022 and 2/15/2022

Would I have to get the max value of t_stamp for 2/10 then get the min value of t_stamp for 2/16 and use those values for my lookup?

thx and sorry..

Jeffrey Williams SSC Guru Points: 90313 More actions · Answer 14

Pretty sure I provided an example already.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Bruin SSCrazy Points: 2091 More actions · Answer 15

Looking for your responses to find that example, but am I correct with what I suggested above.