DrewPierce

Untitled

Oct 15th, 2016
94
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 1.33 KB | None | 0 0
  1. create table people
  2. (   id int auto_increment primary key,
  3.     name varchar(100) not null,
  4.     birthdate timestamp not null -- uses a TIMESTAMP as requested
  5. );
  6. truncate table people;
  7. insert people (name,birthdate) values ('John',current_timestamp());
  8. insert people (name,birthdate) values ('Kim','1997-10-16');
  9. insert people (name,birthdate) values ('James','1997-10-14');
  10. insert people (name,birthdate) select 'Mary','1990-01-04';
  11. insert people (name,birthdate) select 'Sally','1980-06-30';
  12.  
  13. select * from people;
  14. +----+-------+---------------------+
  15. | id | name  | birthdate           |
  16. +----+-------+---------------------+
  17. |  1 | John  | 2016-10-15 19:04:41 |
  18. |  2 | Kim   | 1997-10-16 00:00:00 |
  19. |  3 | James | 1997-10-14 00:00:00 |
  20. |  4 | Mary  | 1990-01-04 00:00:00 |
  21. |  5 | Sally | 1980-06-30 00:00:00 |
  22. +----+-------+---------------------+
  23.  
  24. select id,
  25. name,
  26. birthdate,
  27. datediff(now(),date_add(birthdate,interval 19 year)) as diffInDays
  28. from people
  29. having diffInDays>0;
  30. +----+-------+---------------------+------------+
  31. | id | name  | birthdate           | diffInDays |
  32. +----+-------+---------------------+------------+
  33. |  3 | James | 1997-10-14 00:00:00 |          1 |
  34. |  4 | Mary  | 1990-01-04 00:00:00 |       2841 |
  35. |  5 | Sally | 1980-06-30 00:00:00 |       6317 |
  36. +----+-------+---------------------+------------+
Advertisement
Add Comment
Please, Sign In to add comment