Query SQL Date Time With Epoch

This topic contains 2 replies, has 2 voices, and was last updated by  Keith 4 months, 3 weeks ago.

  • Author
    Posts
  • #67084

    Keith
    Participant

    Hello,

    I am created a script that queries a SQL database we use for our ticketing system. The script connects to an ODBC and queries the selected groups tickets and is supposed to search from date ranges (I am trying just a few days but it could be from a few weeks to months). I have it all complete except the fact when I query tickets it pulls every ticket instead of the ranges. The database does date/time in epoch format and since I am fairly new to SQL and PS I am not sure how to fit that into a SQL select function.

    Can anyone please offer some suggestions?

    Here is my code:

    #When attempting queries - Most are HPD:Help Desk - That will not work here.  It needs to be
    #HPD_Help_Desk
    
    ## Login Information - Using CAC Credentials doesn't work yet - have to use Username and Password
    $id = Get-Credential "domain\$("username")"
    $userId = $id.UserName.split("\")[1].tolower()
    $password = $id.GetNetworkCredential().Password 
        write-host "Running Query"
    
        ##### Start the database connection and set up environment
        $DbString="DSN=ODBC Driver;UID=$userID;PWD=$password"
        add-type -path 'PathtoDll'
        $DBConnection=New-Object System.Data.Odbc.OdbcConnection ($SelectStatement)
        $DBCommand=New-Object System.Data.Odbc.OdbcCommand
        $DBConnection.ConnectionString=$DbString
        $DBConnection.Open()
        $DBCommand.Connection=$DBConnection
    
    
    ##What You Want to Query
        $DBCommand.CommandText="SELECT 
        Incident_Number,
        Assignee, 
        Reported_Date  FROM 
        HPD_Help_Desk WHERE 
        Assigned_Group ='1'
        OR Assigned_Group ='2'
        OR Assigned_Group ='3'
        OR Assigned_Group ='4'
        AND Reported_Date = 'to date'"
        
    ## Execute Query
        $DBResult=$DBCommand.ExecuteReader()
        $UserTable=New-Object system.data.datatable
        $UserTable.load($DBResult) 
        $UserTable | Format-Table
        
      
    

    Any help or suggestions would be appreciated!

    Thank you!

  • #67131

    Matt Bloomfield
    Participant

    Which epoch? There are several in computing and depending on which one your database uses it may be easy or hard(ish) to calculate.

    If it uses seconds since the UNIX epoch (January 1st 1970) then it's straightforward as you can use Get-Date and the -UFormat parameter to return the number of seconds elapsed since epoch for any given date:

    #Number of seconds since UNIX epoch right now
    Get-Date -UFormat %s
    
    #Number of seconds since UNIX epoch on Jan 2nd 1970
    Get-Date '02/01/1970' -UFormat %s

    You can assign the result to a variable and include it in your query:

    $date = Get-Date '23/03/2017' -UFormat %s
    
    ##What You Want to Query
        $DBCommand.CommandText="SELECT 
        Incident_Number,
        Assignee, 
        Reported_Date  FROM 
        HPD_Help_Desk WHERE 
        Assigned_Group ='1'
        OR Assigned_Group ='2'
        OR Assigned_Group ='3'
        OR Assigned_Group ='4'
        AND Reported_Date = '$date'"
  • #67411

    Keith
    Participant

    Thanks Matt,

    Since I am exporting it to a CSV I changed the way the $UserTable is used on the end to:
    $UserTable | Where{$_.Reported_Date -gt ((Get-Date).AddDays(-$DateRange))}| Export-CSV "filepath\test.csv"

    Where $DateRange is I have it to where the user is able to enter however many days they want to go back. I am going to create another version of what you provided too.

You must be logged in to reply to this topic.