adam/Piwigo
1
0 Fork 0
mirror of https://github.com/Piwigo/Piwigo.git synced 2026-10-30 21:52:00 +01:00
Code Issues 657 Packages Projects Releases Wiki Activity

Mysql complains about "Incorrect datetime value" due to weird exif date #891

New Issue
Open
opened 2026-10-05 00:22:56 +02:00 by adam · 3 comments
adam commented 2026-10-05 00:22:56 +02:00
Owner
Copy link
Copy source

Hi,

while trying to batch-import an archive of old photos, the Piwigo folder sync stumbled over several of my photos having weird exif dates. It appears that there are edge cases when my Android phone stored exif date-times such as Date/Time Original: 2016:02:02 24:28:33.863 (note that it's later than 24:00:00) and then MySQL complains about this being invalid.

Strangely, this happened with two different Android phones over the years.

Hi, while trying to batch-import an archive of old photos, the Piwigo folder sync stumbled over several of my photos having weird exif dates. It appears that there are edge cases when my Android phone stored exif date-times such as `Date/Time Original: 2016:02:02 24:28:33.863` (note that it's later than 24:00:00) and then MySQL complains about this being invalid. Strangely, this happened with two different Android phones over the years. <!-- github-sync-id:444977107 -->
adam commented 2026-10-05 00:22:57 +02:00
Author
Owner
Copy link
Copy source

This is mysql 5.7.26 with PHP 7.2, exiftool 11.16 and most recent release of Piwigo.

This is mysql 5.7.26 with PHP 7.2, exiftool 11.16 and most recent release of Piwigo. <!-- github-comment-id:493082819 -->
adam commented 2026-10-05 00:22:58 +02:00
Author
Owner
Copy link
Copy source

exiftool:

======== IMG_20160202_002832.jpg
ExifTool Version Number         : 11.16
File Name                       : IMG_20160202_002832.jpg
Directory                       : .
File Size                       : 1241 kB
File Modification Date/Time     : 2018:11:08 15:00:50+01:00
File Access Date/Time           : 2019:05:16 15:46:07+02:00
File Inode Change Date/Time     : 2019:05:16 15:45:27+02:00
File Permissions                : rw-r--r--
File Type                       : JPEG
File Type Extension             : jpg
MIME Type                       : image/jpeg
Exif Byte Order                 : Big-endian (Motorola, MM)
Modify Date                     : 2016:02:02 24:28:33
GPS Latitude Ref                : North
GPS Date Stamp                  : 2016:02:01
GPS Time Stamp                  : 23:28:16
GPS Longitude Ref               : East
Camera Model Name               : Nexus 4
Y Cb Cr Positioning             : Centered
Resolution Unit                 : inches
Y Resolution                    : 72
Orientation                     : Horizontal (normal)
Color Space                     : sRGB
Create Date                     : 2016:02:02 24:28:33
F Number                        : 2.6
Focal Length                    : 4.6 mm
Aperture Value                  : 2.5
White Balance                   : Auto
Exif Image Width                : 3264
Sub Sec Time                    : 863
Date/Time Original              : 2016:02:02 24:28:33
Sub Sec Time Digitized          : 863
Components Configuration        : Y, Cb, Cr, -
Exif Image Height               : 2448
Flash                           : No Flash
Exif Version                    : 0220
Interoperability Index          : R98 - DCF basic file (sRGB)
Interoperability Version        : 0100
ISO                             : 700
Flashpix Version                : 0100
Sub Sec Time Original           : 863
Exposure Time                   : 1/59
X Resolution                    : 72
Make                            : LGE
Thumbnail Length                : 4048
Thumbnail Offset                : 805
Compression                     : JPEG (old-style)
Image Width                     : 3264
Image Height                    : 2448
Encoding Process                : Baseline DCT, Huffman coding
Bits Per Sample                 : 8
Color Components                : 3
Y Cb Cr Sub Sampling            : YCbCr4:2:0 (2 2)
Aperture                        : 2.6
GPS Date/Time                   : 2016:02:01 23:28:16Z
GPS Latitude                    : ***
GPS Longitude                   : ***
GPS Position                    : ***
Image Size                      : 3264x2448
Megapixels                      : 8.0
Shutter Speed                   : 1/59
Create Date                     : 2016:02:02 24:28:33.863
Date/Time Original              : 2016:02:02 24:28:33.863
Modify Date                     : 2016:02:02 24:28:33.863
Thumbnail Image                 : (Binary data 4048 bytes, use -b option to extract)
Focal Length                    : 4.6 mm
Light Value                     : 5.9
exiftool: ``` ======== IMG_20160202_002832.jpg ExifTool Version Number : 11.16 File Name : IMG_20160202_002832.jpg Directory : . File Size : 1241 kB File Modification Date/Time : 2018:11:08 15:00:50+01:00 File Access Date/Time : 2019:05:16 15:46:07+02:00 File Inode Change Date/Time : 2019:05:16 15:45:27+02:00 File Permissions : rw-r--r-- File Type : JPEG File Type Extension : jpg MIME Type : image/jpeg Exif Byte Order : Big-endian (Motorola, MM) Modify Date : 2016:02:02 24:28:33 GPS Latitude Ref : North GPS Date Stamp : 2016:02:01 GPS Time Stamp : 23:28:16 GPS Longitude Ref : East Camera Model Name : Nexus 4 Y Cb Cr Positioning : Centered Resolution Unit : inches Y Resolution : 72 Orientation : Horizontal (normal) Color Space : sRGB Create Date : 2016:02:02 24:28:33 F Number : 2.6 Focal Length : 4.6 mm Aperture Value : 2.5 White Balance : Auto Exif Image Width : 3264 Sub Sec Time : 863 Date/Time Original : 2016:02:02 24:28:33 Sub Sec Time Digitized : 863 Components Configuration : Y, Cb, Cr, - Exif Image Height : 2448 Flash : No Flash Exif Version : 0220 Interoperability Index : R98 - DCF basic file (sRGB) Interoperability Version : 0100 ISO : 700 Flashpix Version : 0100 Sub Sec Time Original : 863 Exposure Time : 1/59 X Resolution : 72 Make : LGE Thumbnail Length : 4048 Thumbnail Offset : 805 Compression : JPEG (old-style) Image Width : 3264 Image Height : 2448 Encoding Process : Baseline DCT, Huffman coding Bits Per Sample : 8 Color Components : 3 Y Cb Cr Sub Sampling : YCbCr4:2:0 (2 2) Aperture : 2.6 GPS Date/Time : 2016:02:01 23:28:16Z GPS Latitude : *** GPS Longitude : *** GPS Position : *** Image Size : 3264x2448 Megapixels : 8.0 Shutter Speed : 1/59 Create Date : 2016:02:02 24:28:33.863 Date/Time Original : 2016:02:02 24:28:33.863 Modify Date : 2016:02:02 24:28:33.863 Thumbnail Image : (Binary data 4048 bytes, use -b option to extract) Focal Length : 4.6 mm Light Value : 5.9 ``` <!-- github-comment-id:493083139 -->
adam commented 2026-10-05 00:22:58 +02:00
Author
Owner
Copy link
Copy source

exiftool:

======== IMG_20190101_000418.jpg
ExifTool Version Number         : 11.16
File Name                       : IMG_20190101_000418.jpg
Directory                       : .
File Size                       : 3.1 MB
File Modification Date/Time     : 2019:01:01 00:04:20+01:00
File Access Date/Time           : 2019:05:16 12:12:53+02:00
File Inode Change Date/Time     : 2019:02:13 09:49:33+01:00
File Permissions                : rw-r--r--
File Type                       : JPEG
File Type Extension             : jpg
MIME Type                       : image/jpeg
Exif Byte Order                 : Big-endian (Motorola, MM)
Camera Model Name               : Nokia 7 plus
Modify Date                     : 2019:01:01 24:04:20
Orientation                     : Rotate 90 CW
F Number                        : 1.8
Warning                         : [minor] Unrecognized MakerNotes
Exposure Time                   : 1/25
Sub Sec Time Digitized          : 403
Sub Sec Time Original           : 403
Sub Sec Time                    : 403
Focal Length                    : 4.3 mm
Flash                           : No Flash
ISO                             : 320
Metering Mode                   : Center-weighted average
Scene Capture Type              : Unknown (8)
Create Date                     : 2019:01:01 24:04:20
Date/Time Original              : 2019:01:01 24:04:20
White Balance                   : Auto
Aperture Value                  : 1.7
Exif Version                    : 0220
GPS Latitude Ref                : North
GPS Longitude Ref               : East
GPS Altitude                    : ***
GPS Time Stamp                  : 23:04:20
GPS Date Stamp                  : 2018:12:31
Make                            : HMD Global
Thumbnail Offset                : 693
Thumbnail Length                : 19064
JFIF Version                    : 1.01
Resolution Unit                 : None
X Resolution                    : 1
Y Resolution                    : 1
Image Width                     : 4032
Image Height                    : 3024
Encoding Process                : Baseline DCT, Huffman coding
Bits Per Sample                 : 8
Color Components                : 3
Y Cb Cr Sub Sampling            : YCbCr4:2:0 (2 2)
Aperture                        : 1.8
GPS Date/Time                   : 2018:12:31 23:04:20Z
GPS Latitude                    : ***
GPS Longitude                   : ***
GPS Position                    : ***
Image Size                      : 4032x3024
Megapixels                      : 12.2
Shutter Speed                   : 1/25
Create Date                     : 2019:01:01 24:04:20.403
Date/Time Original              : 2019:01:01 24:04:20.403
Modify Date                     : 2019:01:01 24:04:20.403
Thumbnail Image                 : (Binary data 19064 bytes, use -b option to extract)
Focal Length                    : 4.3 mm
Light Value                     : 4.6
exiftool: ``` ======== IMG_20190101_000418.jpg ExifTool Version Number : 11.16 File Name : IMG_20190101_000418.jpg Directory : . File Size : 3.1 MB File Modification Date/Time : 2019:01:01 00:04:20+01:00 File Access Date/Time : 2019:05:16 12:12:53+02:00 File Inode Change Date/Time : 2019:02:13 09:49:33+01:00 File Permissions : rw-r--r-- File Type : JPEG File Type Extension : jpg MIME Type : image/jpeg Exif Byte Order : Big-endian (Motorola, MM) Camera Model Name : Nokia 7 plus Modify Date : 2019:01:01 24:04:20 Orientation : Rotate 90 CW F Number : 1.8 Warning : [minor] Unrecognized MakerNotes Exposure Time : 1/25 Sub Sec Time Digitized : 403 Sub Sec Time Original : 403 Sub Sec Time : 403 Focal Length : 4.3 mm Flash : No Flash ISO : 320 Metering Mode : Center-weighted average Scene Capture Type : Unknown (8) Create Date : 2019:01:01 24:04:20 Date/Time Original : 2019:01:01 24:04:20 White Balance : Auto Aperture Value : 1.7 Exif Version : 0220 GPS Latitude Ref : North GPS Longitude Ref : East GPS Altitude : *** GPS Time Stamp : 23:04:20 GPS Date Stamp : 2018:12:31 Make : HMD Global Thumbnail Offset : 693 Thumbnail Length : 19064 JFIF Version : 1.01 Resolution Unit : None X Resolution : 1 Y Resolution : 1 Image Width : 4032 Image Height : 3024 Encoding Process : Baseline DCT, Huffman coding Bits Per Sample : 8 Color Components : 3 Y Cb Cr Sub Sampling : YCbCr4:2:0 (2 2) Aperture : 1.8 GPS Date/Time : 2018:12:31 23:04:20Z GPS Latitude : *** GPS Longitude : *** GPS Position : *** Image Size : 4032x3024 Megapixels : 12.2 Shutter Speed : 1/25 Create Date : 2019:01:01 24:04:20.403 Date/Time Original : 2019:01:01 24:04:20.403 Modify Date : 2019:01:01 24:04:20.403 Thumbnail Image : (Binary data 19064 bytes, use -b option to extract) Focal Length : 4.3 mm Light Value : 4.6 ``` <!-- github-comment-id:493083673 -->
Sign in to join this conversation.
No Branch/Tag Specified
Labels
No items
No labels
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
adam (Adam Wojcieszonek)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: adam/Piwigo#891