在 MS Access SQL 查询中从正常日期转换为 unix 纪元日期
我正在尝试编写一个通过 ODBC 连接到 MySQL 数据库的 MS Access 2007 连接的查询。一切工作正常，查询执行我想要的操作。我挂断的部分是我一直在询问用户 unix 纪元时间，而不是常规日期。
我查找了 MS Access 的大量参考资料，虽然我可以在 SQL 调用中使用许多日期转换函数，但我找不到任何可以用来从正常日期转换的函数 -> Unix 纪元日期。
我想要的（并且我认为这可行）是以更友善的方式向用户询问日期（人类可读的日期），然后将其转换为 unix 纪元日期。现在我想起来了，我想我的另一个选择是在使用 SQL 查询绘制出数据库中的 unix 纪元日期后将其转换，但我宁愿在可能的情况下转换用户的输入，因为这样的输入较少输入，这样我就不必做那么多的工作。
SELECT TOP 5 Count( * ) AS [Number of visits by language], login.lang AS [Language]
FROM login, reservations, reservation_users
WHERE (reservations.start_date Between [Starting unix epoch time] And [Ending unix epoch time]) And reservations.is_blackout=0 And reservation_users.memberid=login.memberid And reservation_users.resid=reservations.resid And reservation_users.invited=0
GROUP BY login.lang
ORDER BY Count( * ) DESC;
I'm trying to write a query for an MS Access 2007 connection to a MySQL database through ODBC. Everything's working fine, and the query does what I want it to do. The part that I'm hung up on is that I'm stuck asking the user for unix epoch time, instead of a regular date.
I looked up a bunch of references for MS Access, and while there are a number of date conversion functions I can use in the SQL call, I can't find any that I can use to convert from a normal date -> unix epoch date.
What I would like, and I assume this works, is to ask the user for the date in a much kinder fashion (a human readable date), and then convert it into unix epoch date. Now that I think about it, I guess my other option is to convert the unix epoch dates in the database after drawing them out with the SQL query, but I'd rather convert the user's input if at all possible as there is less of that input so I wouldn't have to do as much work.
SELECT TOP 5 Count( * ) AS [Number of visits by language], login.lang AS [Language]
FROM login, reservations, reservation_users
WHERE (reservations.start_date Between [Starting unix epoch time] And [Ending unix epoch time]) And reservations.is_blackout=0 And reservation_users.memberid=login.memberid And reservation_users.resid=reservations.resid And reservation_users.invited=0
GROUP BY login.lang
ORDER BY Count( * ) DESC;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
不知道这是否会起作用（没有访问权限来确认），但请尝试这里的建议：
这是使用：
这是来自一些评论。
编辑：请参阅 Remou 的评论。
Don't know if this is going to work (don't have Access to confirm), but try the suggestion from here:
which is to use:
This is from some of the comments.
EDIT: See the comment from Remou.
使用 MS 2010 我发现这个有效： DateAdd("s",([epoch timestamp]-21600),#1/1/1970#)
http://www.pcreview.co.uk/forums/convert-epoch-date-t2324318.html
Using MS 2010 I found this works: DateAdd("s",([epoch timestamp]-21600),#1/1/1970#)
http://www.pcreview.co.uk/forums/convert-epoch-date-t2324318.html