fmtdate from Unix Epoch or Excel Epoch number -- feature request

[avilella]

Would it be possible to implement a date converter in fmtdate from Unix Epoch or Excel Epoch?

See below:

echo -e "date\n1737454964\n1737454954" | csvtk fmtdate -f 1 --unix-epoch

echo -e "date\n45674.412777777776\n45674.410474537035" | csvtk fmtdate -f 1 --excel-epoch

See the explanation the ChatGPT Enterprise gave me for this (in bash):

# Input Excel serial date
excel_date="45677.39685185185"

# Define constants
excel_epoch="1900-01-01"
unix_epoch="1970-01-01"

# Convert Excel date to seconds since the Unix epoch
seconds_since_epoch=$(
  awk -v ed="$excel_date" 'BEGIN {
    excel_epoch_offset = 25569;  # Excel epoch in days since Unix epoch
    seconds_per_day = 86400;
    seconds_since_epoch = (ed - excel_epoch_offset) * seconds_per_day;
    print seconds_since_epoch;
  }'
)

# Format the date in YYYY-MM-DD hh:mm:ss
formatted_date=$(date -u -d "@$seconds_since_epoch" +"%Y-%m-%d %H:%M:%S")

echo "$formatted_date"

[shenwei356]

It seems that the package for parsing date (https://github.com/araddon/dateparse) can support it (UNIX epoch)

[moorereason]

Another option is extending mutate3:

sh$ echo -e "date\n1737454964\n1737454954" | csvtk mutate3 -n conv -e 'unixEpoch($date).UTC().Format("2006-01-02 15:04:05")'
date,conv
1737454964,2025-01-21 10:22:44
1737454954,2025-01-21 10:22:34

sh$ echo -e "date\n45677.39685185185\n45674.412777777776\n45674.410474537035" | csvtk mutate3 -n conv -e 'excelEpoch($date).UTC().Format("2006-01-02 15:04:05")'
date,conv
45677.39685185185,2025-01-20 09:31:28
45674.412777777776,2025-01-17 09:54:23
45674.410474537035,2025-01-17 09:51:04