October 31, 2011 at 8:46 am
I have a table with a list of unix timestamps. i'm trying to figure out how to list all values newer than a particular date.
I'd rather not have to duplicate the dateadd expression twice; if its possible. I'm also trying to avoid scanning all the date values in the table.
The only way i can think of is to precalculate a number before hand and add it to the where clause.
select dateadd(ss,cast(left(created_dt,len(created_dt)-3) as int),'1970-01-01') as 'Created'
from filetrans
where Created > 20110901
data - query should return 3 records that show dates newer than 9/1/11
CREATE TABLE [dbo].[FILETRANS](
[CREATED_DT] [decimal](19, 0) NOT NULL DEFAULT ((0))
);
insert into filetrans values (1314779407000);
insert into filetrans values (1315989008000);
insert into filetrans values (1315989008000);
insert into filetrans values (1310113807000);
insert into filetrans values (1310805007000);
insert into filetrans values (1311150607000);
insert into filetrans values (1311323406000);
insert into filetrans values (1312705805000);
insert into filetrans values (1312705805000);
insert into filetrans values (1315384206000);
October 31, 2011 at 9:03 am
Does this work for you?
where created_dt > DATEDIFF(s, '1/1/1970', '9/1/2011')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 31, 2011 at 9:09 am
returns all ten rows when
where created_dt > DATEDIFF(s, '1/1/1970', '9/1/2011')
is used
edit - maybe i need to create a subquery to generate the unix timestamp from a sql datetime.
October 31, 2011 at 9:12 am
I'm not familiar with Unix timestamps, but could you use a calendar/dates table (with appropriate 'XTimestamp' column) to help with this?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 31, 2011 at 9:14 am
From your sample data (nicely done btw) I get three rows.
select dateadd(ss,cast(left(created_dt,len(created_dt)-3) as int),'1970-01-01') as 'Created'
from filetrans
where created_dt > DATEDIFF(s, '1/1/1970', '9/1/2011')
Not really sure what the calculated column you have is trying to accomplish. Are you trying to convert to a readable date?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 31, 2011 at 9:16 am
Phil Parkin (10/31/2011)
I'm not familiar with Unix timestamps, but could you use a calendar/dates table (with appropriate 'XTimestamp' column) to help with this?
It is just the number of seconds since Jan 1, 1970. Rather rudimentary calculation but fairly simple...until the 32bit limitation is exceeded (year 2000 epidemic all over again).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 31, 2011 at 9:17 am
@ Phil - i might be able to but id rather not. its not my db. i think it could be done with just a query
@ Sean - i think the reason why it still returned all records is because the where clause doesn't factor in the three additional zeros that are appended to the timestamp
how can i add three zeros to
DATEDIFF(s, '1/1/1970', '9/1/2011')
edit - i did edit the original post to add the zeros. its possible you have stale data.
October 31, 2011 at 9:25 am
Here is your sample data with the unix timestamp converted to datetime in sql.
select *, DATEADD(s, CREATED_DT, '1/1/1970')
from filetrans
where created_dt > DATEDIFF(s, '1/1/1970', '9/1/2011')
order by created_dt
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 31, 2011 at 9:29 am
i edited the original post shortly after i made it. the timestamps should include three zeros after each. this is why i use a calculated feild to display the data.
the orginal post should now have the correct dataset
im trying to run the following but receive arthimetic error
select DATEDIFF(s, '1/1/1970', '9/1/2011') * 1000
error
Arithmetic overflow error converting expression to data type int.
huh this produces same error
select cast(DATEDIFF(s, '1/1/1970', '9/1/2011') as int) * 1000
October 31, 2011 at 9:32 am
I guess i do have stale data. Is your timestamp in milliseconds?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 31, 2011 at 9:34 am
foscsamuels (10/31/2011)
huh this produces same error
select cast(DATEDIFF(s, '1/1/1970', '9/1/2011') as int) * 1000
That value is too big for an int which is why it is failing.
Assuming your data is milliseconds...
select *, DATEADD(s, CREATED_DT / 1000, '1/1/1970')
from filetrans
where created_dt / 1000 > DATEDIFF(s, '1/1/1970', '9/1/2011')
order by created_dt
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 31, 2011 at 9:50 am
i wouldnt think they are milliseconds because they are always zero. or maybe the software is rounding when creating timestamps.
i've never thought of it that way before.
edit - ill give it a go after lunch.
October 31, 2011 at 9:57 am
foscsamuels (10/31/2011)
i wouldnt think they are milliseconds because they are always zero. or maybe the software is rounding when creating timestamps.i've never thought of it that way before.
edit - ill give it a go after lunch.
For example the data of 9/14/2011 08:30:08.000 is the same as the epoch value of 1315989008. Your data is 1315989008000 which is too big to fit in a unix timestamp because that value is larger than a 32bit integer can hold.
You should probably take a look at this page. http://www.epochconverter.com/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 31, 2011 at 10:58 am
Works like a charm Sean. Thanks for all your help.
October 31, 2011 at 11:04 am
You are welcome. Glad that worked for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply