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/). I hope this helps and Happy New Year! -- C > On Jan 2, 2023, at 7:39 AM, Uwe Geercken <uwe.geerc...@web.de> 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" <uwe.geerc...@web.de> > An: user@drill.apache.org > 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
signature.asc
Description: Message signed with OpenPGP
