Hi Charles, happy new year to you and all others of course. My first encounter with Drill was already years ago but I never really at a good project at work or home, so I did not use it intensively. I did check the status of Drill every now and then though. A few month ago I was looking for a tool to make Json files - stored on S3 (on-prem on Netapp Storage Grid) - available for relational querying. At the time it was decided to store the Json payload in a PostgreSql column, but I still think Drill could be a good/better alternative. The decision was not taken within my team and I did not have time to spend to go deeper into Drill. Now I have some holidays and free time to play around and though I can rediscover Drill a bit. I want to sort my pictures by event using an ETL tool and match the dates of the pictures (from the metadata or the filesystem) to events such as "holiday spain", "birthday Joe", etc. I had an issue with parsing the FileDateTime column from the query in the ETL tool, but have solved it meanwhile. It looks like this is a localized date but it gets returned as a varchar. LMT seems to be the same date but is also returned as a varchar. Anyway, I got it reformat using this: to_date(FileDateTime,'E MMM dd HH:mm:ss ZZ yyyy'). This looks similar to the pattern I use in the ETL tool and I think I will parse it there and not in the sql query.
Thanks for your valuable feedback and the links. Rgds, Uwe Gesendet: Montag, 02. Januar 2023 um 15:43 Uhr Von: "Charles Givre" <[email protected]> An: "user" <[email protected]> Betreff: Re: Image Metadata Format Plugin: FileDateTime Hi Uwe, Welcome to Drill! I'm glad you got that sorted out. Here are a few tricks which might help you out: 1. Drill implicit columns: One thing you might not be aware of is that Drill has some implicit columns which do not appear when you execute a SELECT * query. Different columns exist for different plugins and formats, but for general files, they are: FQN, FILEPATH, FILENAME, SUFFIX, and LMT. Of those, LMT is the Last Modified Time reported by the file system. 2. Reformatting dates and times: Drill has several functions for reformatting times. The first thing you should do however, is verify that the field is coming back as a timestamp or a string. You can use the typeof() function to do this. IE SELECT typeof(field) FROM... LIMIT 1 Assuming it is a TIMESTAMP or DATE, to reformat it, you'll want to use the awfully named TO_CHAR function to reformat the date into a more readable format. (https://drill.apache.org/docs/data-type-conversion/#to_char). You can provide a format string to format the date however you want. If you just want the date, you can use the TO_DATE() function which will strip out the time components and just give you a date in the format of yyyy-MM-dd. If the value is coming back as a string, you mayl need to convert it to a TIMESTAMP or DATE first. Take a look here for more date/time functions (https://drill.apache.org/docs/date-time-functions-and-arithmetic/[https://drill.apache.org/docs/date-time-functions-and-arithmetic/]). I hope this helps and Happy New Year! -- C On Jan 2, 2023, at 7:39 AM, Uwe Geercken <[email protected]> wrote: Hello, I found the answer. First, I said I checked the files on the filesystem, but I checked them in nautilus filemanager on Fedora and it displays the correct date, but I think it displays it from the picture metadata. Looking at the filedate in a terminal the files actually all have a file date of Jan 01 1970. So I figured it was a problem when copying the files over to my Laptop. When I hook up my smartphone,the mtp protocol is used and when copying the files in nautilus filemanager the correct timestamp is not used. This issue was reported in 2020 and marked as fixed, but maybe the issue is still around in the filemanager or another component. The workaround is to go to the folder in the terminal and copy the file manually using "cp -pr *". Still, if somebody could tell me how to reformat the FileDateTime value from the query to a more generic date and time, I would be greatful. Greetings, Uwe Gesendet: Montag, 02. Januar 2023 um 12:51 Uhr Von: "Uwe Geercken" <[email protected]> An: [email protected] Betreff: Image Metadata Format Plugin: FileDateTime Hello, I am using Drill 1.20.2 on Fedora Linux 37. I have setup a query to retrieve the metadata of the pictures I have taken just a few days ago with my smartphone (Samsung S21-FE). I copied the files yesterday from my smartphone to my computer. The query runs in my ETL tool (tweakstreat.io) and I want to retrieve the datetime when the shot was taken(from ExifIFD0) and also use the datetime of the picture on the filesystem. That works well, the only thing strange is that the FileDateTime on all pictures shows: Do. Jan. 01 02:00:00 +01:00 1970 (German format). I have checked the files manually on the filesystem and they do have a correct timestamp (different from 1970) on the filesystem. Where is the FileDateTime value actually comming from?? I assume from the filesystem and not from the picture metadata, right? Even stranger, when I process some older pictures from 2019 e.g., then the FileDateTime is displayed correctly - but I must admit, that I had a different smartphone at that time (though also a Samsung). Can anybody help me with this? A side question is if it is possible to reformat the FileDateTime value returned from the query to a different format. I just simply cannot find a pattern that would work. Regards, Uwe
