12 Replies Latest reply: Apr 18, 2012 2:57 AM by Vinoth Kumar Rajendran RSS

Removing the Time from Timestamp from Google Doc

Dan Granger

I'm trying to remove the time from the Timestamp of a google doc spreadsheet. I need to get the date only for display and grouping purposes.

 

Anyone have an idea?

 

Dan

  • Removing the Time from Timestamp from Google Doc
    Staffan Wessman

    Hi Dan,

     

    To convert a timestamp to a date, you need to round the underlying numeric value to remove the decimals corresponding to the time. After this you convert the numeric value to a date.

     

    Example:
    LOAD
    date(floor(TimestampField)) AS Date
    FROM DataSource;

     

    Kind regards

    Staffan

  • Removing the Time from Timestamp from Google Doc

    Are you trying to get the time or the date? date(floor(timestampfield)) will give you the date associated with the field and would give you a null if you tried to specify it was a timestamp.

     

    If you have something like

     

    12//01/2011 10:55:22

     

    and you wanted to get 10:55:22 you could do the following:

     

    timestamp(right(timestampfield,8))

     

    or

     

    timestamp(subfield(timestampfield,' ',2)).

  • Re: Removing the Time from Timestamp from Google Doc
    Staffan Wessman

    Rob's addition definitely should get this to work for you.

     

    Just wanted to add a note regarding the use of string functions as left() and right() to convert timestamps.
    What you need to keep in mind is that timestamps, dates and times are represented by both a string value and a numeric value.

    Example:
    String | Numeric
    2011-06-07 09:00:00 | 40701.375

    2011-06-07 12:00:00 | 40701.5

     

    If you were to read this field using left(Field, 10), you would strip the time from the string representation but not from the numeric value. The effect this has is that they will be considered as two different values by QlikView and will not link to the same values in other fields.

    To avoid this you should instead modify the numeric value and then convert the field using e.g. date() to the desired string representation.

    • Re: Removing the Time from Timestamp from Google Doc

      swn,

      Thanks for the explaining. One question though. If you use the TimeStamp#() function don't you keep the numerical date but only format the field to a timestamp? If Sith88 is trying to get only get the timestamp, regardless of day, is there any way do this without converting it to a string?

       

      In the past I've had to resort to timestamp#(timestamp(right(F2,8))) to get it to match other true timestamp fields.

      • Re: Removing the Time from Timestamp from Google Doc
        Staffan Wessman

        I think that we are talking about a few different things here actually. As I interpreted the original question, sith88 is looking to read only the date part of the timestamp and not the time. Reading only the time will require a different approach. However, what you in all cases need to keep in mind is that dates, times and timestamps (date + time) include both a string representation and a numeric underlying value and that this needs to be taken in account when converting these values.

         

        A note regarding timestamp#(field[, format]), this function doesn't format the values, but actually only tells QlikView that the field is a timestamp. This applies to date#() and time#() as well.
        These functions are particularly useful when reading values which are not formatted according to your standard system formatting.
        You can nest them together with their formatting equivalent to convert them to your standard format instead. Example:
        date(date#('2011_06_08', 'YYYY_MM_DD'))
        If your system format is DD/MM/YYYY, this would return 08/06/2011.

  • Removing the Time from Timestamp from Google Doc
    Vinoth Kumar Rajendran

    Hi All,

     

    Could you please provide steps or any doucument for fetching data from Google doc. Becuase I  tried but could not success . Thanks lot in advance.

     

    Arunesh