How to Convert a String Containing Am/Pm to Datetime

How to convert string containing AM/PM to datetime in python using %p

This is why I use arrow. Just makes all of this easier.

import arrow
frmt = 'YYYY-MM-DD HH:mm:ss A'
print(arrow.get('2000-01-01 12:00:00 AM', frmt))
print(arrow.get('2000-01-01 12:00:00 PM', frmt))
#datetime
print(arrow.get('2000-01-01 12:00:00 AM', frmt).datetime)
print(arrow.get('2000-01-01 12:00:00 PM', frmt).datetime)
#isoformat
print(arrow.get('2000-01-01 12:00:00 AM', frmt).isoformat())
print(arrow.get('2000-01-01 12:00:00 PM', frmt).isoformat())

How to convert a string containing AM/PM to DateTime?

You should change the hour format (H) to lowercase like this:

DateTime.ParseExact("2/22/2015 9:54:02 AM", "M/d/yyyy h:mm:ss tt", CultureInfo.InvariantCulture);

Uppercase "H" indicates a 24-hour time and lowercase "h" indicates 12-hour time and will respect the AM/PM in the candidate string.

Date Time string with AM/PM conversion to DateTime in Clickhouse

This wrong behavior was fixed starting with ClickHouse version 21.1.2.15.

Unfortunately, it is the specificity of the current implementation. Let's check the source code:

parseDateTimeBestEffort.h

AM/PM - AM is ignored and PM means: add 12 hours if value is less than 12.

(see parseDateTimeBestEffort.cpp)

I created the bug #18402 parseDateTimeBestEffort should not ignore AM abbreviation for 12th hour.

As a workaround, I would suggest this way:

SELECT
    str,
    parseDateTimeBestEffort(str) AS dt,
    if((toHour(dt) = 12) AND (str ILIKE '% am'), subtractHours(dt, 12), dt) AS fixed_dt
FROM 
(
    SELECT '2020-02-01 12:10:00 AM' AS str
    UNION ALL
    SELECT '2020-02-01 12:10:00 am'
    UNION ALL
    SELECT '2020-02-01 12:10:00 Am'
    UNION ALL
    SELECT '2020-02-01 12:10:00 aM'
    UNION ALL
    SELECT '2020-02-01 12:10:00 PM'
)
ORDER BY fixed_dt

/*
┌─str────────────────────┬──────────────────dt─┬────────────fixed_dt─┐
│ 2020-02-01 12:10:00 AM │ 2020-02-01 12:10:00 │ 2020-02-01 00:10:00 │
│ 2020-02-01 12:10:00 am │ 2020-02-01 12:10:00 │ 2020-02-01 00:10:00 │
│ 2020-02-01 12:10:00 Am │ 2020-02-01 12:10:00 │ 2020-02-01 00:10:00 │
│ 2020-02-01 12:10:00 aM │ 2020-02-01 12:10:00 │ 2020-02-01 00:10:00 │
│ 2020-02-01 12:10:00 PM │ 2020-02-01 12:10:00 │ 2020-02-01 12:10:00 │
└────────────────────────┴─────────────────────┴─────────────────────┘

*/

Converting python string to datetime obj with AM/PM

datetime.strptime() is used for converting a string to a datetime object , when using strptime() you have to specify the correct format in which the date/time in the string exists .

In your case the format should be - '%Y-%m-%d %H:%M:%S' .

Example -

>>> test = '2015-08-12 13:07:32'
>>> import datetime
>>> datetime.datetime.strptime(test, '%Y-%m-%d %H:%M:%S')
datetime.datetime(2015, 8, 12, 13, 7, 32)

If what you really want is the date-time back as a string with the AM/PM , then you need to use strftime() to convert it back to string with the format you want, in this case the format would be - '%Y-%m-%d %I:%M:%S %p' . Example -

>>> datetime.datetime.strptime(test, '%Y-%m-%d %H:%M:%S').strftime('%Y-%m-%d %I:%M:%S %p')
'2015-08-12 01:07:32 PM'

datetime objects internally do not store (and do not have to store) the AM/PM information, since that can be easily calculated from the hour.

How to parse "string" to "DateTime" with "a. m." or "p. m." format (not AM/PM)?

This is what the tt custom format specifier are for.

var date = "6/01/2018  12:00:03 am";
var x = DateTime.ParseExact(date, "d/MM/yyyy  h:mm:ss tt", CultureInfo.InvariantCulture);

But remember, this tt specifier does not parse a. m. or a.m. strings. If your strings have those, you have to manipulate your strings like removing dots and/or spaces between a and m etc.. It also parse AM and PM as well.

String with AM/PM to Date

You have defined wrong format.
Because you are defining your date in AM/PM format, you can't pass hrs as 24 (HH).

format = "dd-MMM-yyyy hh:mm:ss a"; //small case hh

Above is what you should use.

How can I account for AM/PM in string to DateTime conversion in pyspark?

You can do like below to achieve your result

from pyspark.sql import Row

df = sc.parallelize([Row(visit_dts='5/1/2018 3:48:14 PM')]).toDF()

import pyspark.sql.functions as f

web = df.withColumn("web_datetime", f.from_unixtime(f.unix_timestamp("visit_dts",'MM/dd/yyyy hh:mm:ss aa'),'MM/dd/yyyy HH:mm:ss'))

This should give you

web.show()

+-------------------+-------------------+
|          visit_dts|       web_datetime|
+-------------------+-------------------+
|5/1/2018 3:48:14 PM|05/01/2018 15:48:14|
+-------------------+-------------------+

How to cast string with AM/PM to timestamp in pandas

You can drop dayfirst, because that information is included in your formatting string. To include your entire datestring, including .548518 PM, use the %f and %p directives:

pd.to_datetime('29-08-17 11.34.58.548518 PM', format='%d-%m-%y %I.%M.%S.%f %p')

Returning:

Timestamp('2017-08-29 23:34:58.548518')

%f stands for Microsecond as a decimal number, and %p stands for Locale’s equivalent of either AM or PM (see docs for more)