Results 1 to 8 of 8

Thread: Where logtime

  1. #1
    Join Date
    Jan 2005
    Location
    Zurich Switzerland
    Posts
    17

    Unanswered: Where logtime

    Hi! all..
    I would like to use select statment to retrieve all the values that are timestamp 1 hour ago from Logetable. Any Idea..

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Select field
    from table
    where timestampfield = DateAdd(hh,-1,getdate())
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2005
    Location
    Zurich Switzerland
    Posts
    17

    datetime

    Quote Originally Posted by Thrasymachus
    Select field
    from table
    where timestampfield = DateAdd(hh,-1,getdate())
    Hi! thanks will the same code work if I have datetime as field type

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think Thrasy meant a datetime field. Technically, a true timestamp value wouldn't work.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6

    my bad

    Whoops. I was an application developer long before someone gave me the DBA title. I always waste time and resources by adding my own "time stamp" and "user stamp" from my application end and not letting SQL Server do it. Out of habit I guess. So I never ran into this incompatibility.

    Sorry.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Jan 2005
    Location
    Zurich Switzerland
    Posts
    17

    Hourly loggin

    Hi! Actually I want to get the number of students who atteded the prep with the last 1 hour.

    CREATE TABLE [dbo].[PrepTime] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [PrepNo] [int] NOT NULL ,
    [Log_Time] datetime NOT NULL DEFAULT, CURRENT_TIMESTAMP,
    [coming] [int] NULL ,
    [going] [int] NULL ,
    [Student_ID] [bigint] NULL ,
    [Study_Type] [int] NULL ,
    [State] [smallint] NULL

    ) ON [PRIMARY]
    GO

  7. #7
    Join Date
    Dec 2004
    Location
    CA, USA
    Posts
    63
    Within the last hour? Building on what Thras said:

    select count(distinct(student_id)) --drop the distinct if student_id is unique
    from table
    where log_time between dateadd(hh, -1, getdate()) and getdate()
    Last edited by pshisbey; 01-05-05 at 15:18.

  8. #8
    Join Date
    Jan 2005
    Location
    Zurich Switzerland
    Posts
    17

    Thanks

    Quote Originally Posted by pshisbey
    Within the last hour? Building on what Thras said:

    select count(distinct(student_id)) --drop the distinct if student_id is unique
    from table
    where log_time between dateadd(hh, -1, getdate()) and getdate()
    Thank you for your help it worked :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •