A year to Unix Time in MySQL

Question

Having difficulties when converting a year to unix time in MySQL.

This is my table:

CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `born_date` int(45) DEFAULT NULL,
  `unix_born_date` varchar(100) DEFAULT NULL,
  `death_date` varchar(45) DEFAULT NULL,
  `unix_death_date` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8;

In the field born_date I have years, like «1864». I want to convert them to Unix Time in a query, something like:

SELECT id, UNIX_TIMESTAMP(STR_TO_DATE(born_date, '%Y')) AS Unixtime
FROM person

But it returns 0…

Does anyone has an idea what to do to convert the year to Unix Time?

N.

`UNIX_TIMESTAMP(NOW())` works, but when I write `UNIX_TIMESTAMP(STR_TO_DATE(born_date, '%Y'))`, it returns a 0… — , May 25 '17 at 16:23
Born date isn't a valid date. If you had a day/month then you'd have a born date. as it stands you have a Born Year, and year byitself is insufficient to convert to a unix time stamp... you could add Jan 1 as the month year and it should work. or keep it as a int instead of a date. since you can only work with year. Think about it a year is how many hours minutes/seconds... which one should unix timestamp use? Maybe if you had week... https://stackoverflow.com/questions/5763340/how-to-convert-week-number-and-year-into-unix-timestamp — xQbert, May 25 '17 at 16:23
There are seriously errors in your design. `born_date` should be date not int, unix_born_date should be timestamp not varchar, death_date should be date or datetime not varchar and unix_death_date should be timestamp. AND if you create the fields with appropriate type you wont need then duplicated as you have now. — Jorge Campos, May 25 '17 at 16:24
You are in reason Jorge, I will make this changes in a moment… — , May 25 '17 at 16:27

score 0 · Answer 1 · answered May 25 '17 at 16:25

0

when you pass the date, the format need meeds to be YYYY-MM-DD

select id,UNIX_TIMESTAMP(concat(date_format(born_date,'%Y'),'-01-01')

answered May 25 '17 at 16:25

israel

350
1
2
9

`SELECT id, UNIX_TIMESTAMP(CONCAT(DATE_FORMAT(born_date,'%Y'),'-01-01'))` returns a NULL, and `SELECT id, UNIX_TIMESTAMP(DATE_FORMAT(CONCAT(born_date,'-01-01'),'%Y-%m-%d'))` returns a 0… – May 25 '17 at 16:30
sorry , the format isn't what i thought , try UNIX_TIMESTAMP(CONCAT(STR_TO_DATE(born_date, '%Y'),'-01-01')) – israel May 25 '17 at 16:33
`SELECT id, UNIX_TIMESTAMP(DATE_FORMAT(CONCAT(born_date,'-01-01'),'%Y-%m-%d'))` works, but only for dates after 1970! – May 25 '17 at 16:37
its since 1970 in W3C http://www.w3resource.com/mysql/date-and-time-functions/mysql-unix_timestamp-function.php "Linux is following the tradition set by Unix of counting time in seconds since its official "birthday," -- called "epoch" in computing terms -- which is Jan. 1, 1970." – israel May 25 '17 at 16:40

score 0 · Answer 2 · answered May 25 '17 at 16:31

Among other things, because UNIX_TIMESTAMP() function in MySQL cannot handle year 1864:

The valid range of values is the same as for the TIMESTAMP data type: '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC.

It doesn't help either that STR_TO_DATE() populates missing data with zeroes:

Unspecified date or time parts have a value of 0, so incompletely specified values in str produce a result with some or all parts set to 0

SELECT 
STR_TO_DATE(1864, '%Y') AS d1, UNIX_TIMESTAMP(STR_TO_DATE(1864, '%Y')) AS u1,
    FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE(1864, '%Y'))) AS f1,
STR_TO_DATE(2017, '%Y') AS d2, UNIX_TIMESTAMP(STR_TO_DATE(2017, '%Y')) AS u2,
    FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE(2017, '%Y'))) AS f2;

+------------+------+---------------------+------------+------------+---------------------+
| d1         | u1   | f1                  | d2         | u2         | f2                  |
+------------+------+---------------------+------------+------------+---------------------+
| 1864-00-00 |    0 | 1970-01-01 01:00:00 | 2017-00-00 | 1480460400 | 2016-11-30 00:00:00 |
+------------+------+---------------------+------------+------------+---------------------+

Whatever you have in mind, you cannot do it with MySQL.

That's it! All the people of my table where born before 1970! So I'll do it in PHP… — , May 25 '17 at 16:33
I found the way! `SELECT id, DATEDIFF(STR_TO_DATE(CONCAT('1-1-',born_date),'%d-%m-%Y'),FROM_UNIXTIME(0))*24*3600 AS born_date_unix` — , May 25 '17 at 18:00

score 0 · Answer 3 · answered May 25 '17 at 18:02

I found the answer here: Converting a date string which is before 1970 into a timestamp in MySQL.

SELECT id, DATEDIFF(STR_TO_DATE(CONCAT('1-1-',born_date),'%d-%m-%Y'),FROM_UNIXTIME(0))*24*3600 AS born_date_unix

And it works!

N.

A year to Unix Time in MySQL

3 Answers3