0

I'm working with a date string in this format Wednesday 21 February 2018 - 09:35. I'm just curious, is there an easy way to change this into a UNIX datetime format in either SQl or PHP or would I have to write a unique case for each one.

I have tried changing this with the PHP function strtotime but I can't get it to work with this specific format.

Thanks!

Cameron
  • 65
  • 1
  • 10

1 Answers1

2

You can actually handle this directly in MySQL, using the STR_TO_DATE and UNIX_TIMESTAMP functions:

SELECT UNIX_TIMESTAMP(
    STR_TO_DATE('Wednesday 21 February 2018 - 09:35', '%W %e %M %Y - %H:%i')) AS ts
FROM dual;

enter image description here

Demo

The basic idea is to first convert your text timestamp into a formal date using STR_TO_DATE, then convert that date into a UNIX timestamp using UNIX_TIMESTAMP.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Awesome! Exactly what I was looking for. Do you happen to know how to do this in SQL Server? – Cameron Jun 06 '18 at 03:26
  • @CameronRose Sorry, I thought you were using MySQL (usually a good assumption with a PHP question). No, I don't think SQL Server has the ability to parse an arbitrary timestamp like yours into a formal date. – Tim Biegeleisen Jun 06 '18 at 03:32
  • If you're really using SQL Server, then you'll probably have to handle this in PHP, or just use ISO compliant timestamps. – Tim Biegeleisen Jun 06 '18 at 03:36