Converting String to Date datatype

Working with Data
,
#1

I want to convert existing field of type string having date like "Fri Jun 10 14:05:28 IST 2022"to date type ISO (utc).

i tried $convert , but it is converting it utc by only reducing time by 2 hours, while it should be reducing it by 5 and a half hours when converting IST to utc

Sample document

[

{
        "_id": {
                "$oid": "62a30251c8503f782b572e0c"
        },
     
        "start_time": "Fri Jun 10 14:05:28 IST 2022",
        "stop_time": "undefined",

}

]
#2

The intial format is output of date command in ubuntu

#3

You could avoid all this trouble by using

date --iso-8601=seconds

rather than simply using date.

Them store you dates as Date rather than string. The date datatype uses less space than string and is faster to compare.

#4

And u are right, Wish they listened to me in the beginning xd.

But now i have this ,

#5

Ohh, I feel your pain.

If $toDate or $convert does not provide the appropriate conversion then I would use $dateSubtract to make a final adjustment.

I have played around trying to $dateToString to convert back an IST date stored in UTC to an IST string and got:

unrecognized time zone identifier: "IST"

Hopefully, they, once they see the trouble they caused, will listen to you and let you use the appropriate format.

1 Like
#6

$convert and $dateFromString both support timezone option - is there a reason not to use that?

Asya

#7

Yes, now moving forward we are going to dump data in ISO date datatype only

1 Like
#8

I for one, presumed that the timezone was inferred by the presence of IST in the source string.

If I try with

{ "$dateFromString" : { "dateString" : "$start_date" , "timezone" : "IST"} } }

I get:

MongoServerError: PlanExecutor error during aggregation :: caused by :: unrecognized time zone identifier: “IST”

If I try the alternative way to specify the timezone with

{ "$dateFromString" : { "dateString" : "$start_date" , "timezone" : "-0530"} } }

I get:

MongoServerError: PlanExecutor error during aggregation :: caused by :: you cannot pass in a date/time string with time zone information (‘IST’) together with a timezone argument

So it would seems like $dateFromString correctly infers IST as the timezone to use.

Replacing IST in the source string using the alternative way to specify the timezone works. The following string works without specifying the timezone in $dateFromString:

'Fri Jun 10 14:05:28 -0530 2022'

Alternatively the string 'Fri Jun 10 14:05:28 2022' also works when specifying “-0530” in $dateFromString.