【mongoDB】用shell脚本转换mongoDB时间

1. MongoDB 中时间格式 ISODate("2023-01-13T08:51:07.148Z") ， 将其转换为北京时间

可以使用shell脚本循环处理多个时间。具体代码如下：

#!/bin/bash

# 原始时间数组
original_times=(
    '2023-01-13T08:51:07.148Z'
    '2023-01-14T09:52:08.149Z'
    '2023-01-15T10:53:09.150Z'
)

# 循环处理每个时间
for original_time in "${original_times[@]}"
do
    # 将字符串转换为时间戳
    timestamp=$(date -d "$original_time" +%s)

    # 转换为北京时间
    beijing_time=$(date -d @$timestamp +'%Y-%m-%d %H:%M:%S')

    # 输出转换后的时间字符串
    echo $beijing_time
done

这里我们定义了一个原始时间数组，循环处理每个时间，转换为北京时间并输出。输出结果如下：

2023-01-13 16:51:07
2023-01-14 17:52:08
2023-01-15 18:53:09

2. 将MongoDB 导出的csv文件中时间转换为北京时间

$ cat file.csv

bizId,createTime
89821123963c0ba6de4b095b2050da3851676515883290,2023-02-16T02:51:23.354Z
89821123963c0ba6de4b095b2050da3851676515921659,2023-02-16T02:52:01.744Z
FWOA21222163c0ba5fe4b095b2050d7a731676517257598,2023-02-16T03:14:17.658Z
FWOA21222563c0ba5fe4b095b2050d7af51676517258518,2023-02-16T03:14:18.565Z
FWOA21222763c0ba5fe4b095b2050d7ad01676517258956,2023-02-16T03:14:19.002Z
94321223463c0ba7ee4b0273b933bdb7c1676527745681,2023-02-16T06:09:05.730Z
73621224163c0ba4de4b0273b933b85b91676532139754,2023-02-16T07:22:19.795Z
FWOA21622563c0ba75e4b0273b933bc87e1676598007773,2023-02-17T01:40:07.821Z
FWOA21622763c0ba7ee4b0273b933bdc981676598008301,2023-02-17T01:40:08.348Z
FWOA21622963c0ba85e4b0273b933bec111676598008693,2023-02-17T01:40:08.744Z

$ cat trans_timeformat.sh

#!/bin/bash

# 读取file.csv文本
while IFS=',' read -r col1 col2
do
  # 判断是否为第2列，且不是标题行
  if [[ "$col2" == "bizId" || "$col2" == "createTime" || -z "$col2" ]]; then
    continue
  fi
  
  # 将时间转换为北京时间
  beijing_time=$(TZ="Asia/Shanghai" date -d "$col2" "+%Y-%m-%d %H:%M:%S")
  
  # 将处理结果放回原处覆盖原来的值
  sed -i "s|$col2|$beijing_time|" file.csv
done < file.csv

image.png

$ cat file.csv

bizId,createTime
89821123963c0ba6de4b095b2050da3851676515883290,2023-02-16 10:52:01
FWOA21222163c0ba5fe4b095b2050d7a731676517257598,2023-02-16 11:14:17
FWOA21222563c0ba5fe4b095b2050d7af51676517258518,2023-02-16 11:14:18
FWOA21222763c0ba5fe4b095b2050d7ad01676517258956,2023-02-16 11:14:19
94321223463c0ba7ee4b0273b933bdb7c1676527745681,2023-02-16 14:09:05
73621224163c0ba4de4b0273b933b85b91676532139754,2023-02-16 15:22:19
73821621763c0ba66e4b095b2050d8d4e1676602597316,2023-02-17 10:56:37
FWOA21721863c0ba41e4b0273b933b6acc1677382412182,2023-02-26 11:33:32

image.png