convert darktable's datetime_taken to real time

Software darktable
1

Hi.

In darktable 5.2 the datetime_taken value in the images table is something like 63880133774380000 which somehow translates to Sun 13/04/25 09:36:14.

Reading the source is beyond me and chatgpt is confidently incorrect.

Can anyone explain.

Thanks.

2 
// The GTimeSpan saved in db is an offset to datetime_origin (0001:01:01 00:00:00)
// Datetime_taken is to be displayed and stored in XMP without time zone conversion
// The other timestamps consider the timezone (GTimeSpan converted from local to UTC)
// The text format of datetime follows the exif format except when local format
2 Likes
3

Thanks Bill but how do I do the conversion in sql or python?

4

Darktable has code to do those conversions (datetime.h/.c, database.h/.c). So that might be a good place to start. But as darktable is C code, you’ll have to translate that to the language you want to use.

Note that darktable does not use the “linux epoch” at time zero!

1 Like
5

Thanks all.

I still can’t get it to work in sql or sqlite but chatgpt came up with some python which works:

from datetime import datetime, timedelta
def ticks_to_datetime(datetime_taken):
ticks_per_second = 1_000_000
seconds, remainder = divmod(datetime_taken, ticks_per_second)
microseconds = remainder // 10
dotnet_epoch = datetime(1, 1, 1)
human_readable = dotnet_epoch + timedelta(seconds=seconds, microseconds=microseconds)
return human_readable

6

Oops.
Copy’n’paste mucked up the whitespace but I’m sure you get the idea.

7

From datetime.c:

#define DT_DATETIME_ORIGIN "0001-01-01 00:00:00.000"
#define DT_DATETIME_EPOCH "1970-01-01 00:00:00.000"

where DT_DATETIME_ORIGIN corresponds to a GTimeSpan == 0,
at least Linux/UNIX timestamps start counting from DT_DATETIME_EPOCH.
You may want to check this for your system.

1 Like
8

thanks

9

I just happened to come across this thread.

No need to consult an AI-driven chatbot. The whole thing can be solved with a simple SQL command and two sqlite3 time and date functions :

SELECT strftime('%F %T', datetime(datetime_taken / 1000000 - 62135596800, 'unixepoch')) FROM images
1 Like