Reputation: 841
I have a variable in the format of string, such as 2019-11-25T14:22:56.529Z. And I need to extract the date part of this string variable and extract all records with the latest date.
ID time (in string format, not a timestamp yet)
-------------------------------
1 2019-11-25T15:20:56.539Z
2 2019-11-25T04:23:56.522Z
3 2019-11-25T11:11:54.509Z
4 2019-12-25T10:22:36.129Z
5 2019-12-25T09:22:29.349Z
6 2019-12-25T08:22:56.209Z
I want this output:
ID time
---------------
4 2019-12-25
5 2019-12-25
6 2019-12-25
In general, I would also like to know how to convert this type of string variable to date format for future sorting by datetime. Thank you!
Upvotes: 0
Views: 1863
Reputation: 2321
you can do achieve it like below :
SELECT ID, FROM_UNIXTIME(UNIX_TIMESTAMP(time, "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"),"yyyy-MM-dd") as time
FROM yourtable
Upvotes: 1
Reputation: 1234
Just use the substring function for this
select substr(time,1,10) as date from tablename;
Update This would convert it back to date and you can do your sorting
select cast(substr(time,1,10) as date) as date from tablename;
Upvotes: 3