Midrange News for the IBM i Community


Posted by: rpgprogrammer
Vancouver, BC
SQL with Timestamp Filtering
has no ratings.
Published: 30 Aug 2013
Revised: 03 Sep 2013 - 1393 days ago
Last viewed on: 27 Jun 2017 (2347 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

SQL with Timestamp Filtering Published by: rpgprogrammer on 30 Aug 2013 view comments(2)

Need a expert advice, I want to find all records from a given file which falls between two timestamp example select * from filename where filefield.timz between '2013-08-31-03.41.51.036000' and '2013-08-31-03.41.49.040000'. Seems the timestamp values are not numeric so bit confused..

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: SQL with Timestamp Filtering
Posted: 3 years 9 months 28 days 3 minutes ago
Edited: Fri, 30 Aug, 2013 at 20:50:48 (1397 days ago)

You have them quoted, so what's the problem? Perhaps TIMESTAMP only goes to milliseconds--try omitting the last 3 zeros and see if it takes it.

Posted by: jjcllhn
Premium member *
Comment on: SQL with Timestamp Filtering
Posted: 3 years 9 months 24 days 20 hours 32 minutes ago
I'm a bit confused the way you reference column filefield in your example. Is it meant to imply that filefield is defined as a timestamp, or that the values of filefield should be interpreted as timestamp values, or is it something else I've failed to consider? It's not typical notation for DB2i. If the column is defined as a timestamp, no qualifier would be needed (What does .timz represent?), SQL will convert to constants to timestamp for comparison. If the column is defined as a numeric, or a character, you'd format the string and convert it to a timestamp value using the timestamp function, and your SELECT statement would look like this: SELECT * FROM filename WHERE TIMESTAMP( formatted_filefield ) BETWEEN ts_value1 AND ts_value2 FWIW, DB2i translates its internal representations of dates, times and timestamps into external values that are locale friendly based on format settings.