Top datetime frequently asked interview questions

MySQL Query GROUP BY day / month / year

Is it possible I make a simple query to count how many records I have in a determined period of time like a Year, month or day, having a TIMESTAMP field, like:

SELECT COUNT(id)
FROM stats
WHERE record_date.YEAR = 2009
GROUP BY record_date.YEAR

Or even:

SELECT COUNT(id)
FROM stats
GROUP BY record_date.YEAR, record_date.MONTH

To have a monthly statistic.

Thanks!

Source: (StackOverflow)

Is DateTime.Now the best way to measure a function's performance?

I need to find a bottleneck and need to accurately as possible measure time.

Is the following code snippet the best way to measure the performance?

DateTime startTime = DateTime.Now;

// Some execution process

DateTime endTime = DateTime.Now;
TimeSpan totalTimeTaken = endTime.Subtract(startTime);

Source: (StackOverflow)

How to convert a Unix timestamp to DateTime and vice versa?

There is this example code, but then it starts talking about millisecond / nanosecond problems.

The same question is on MSDN, Seconds since the Unix epoch in C#.

This is what I've got so far:

public Double CreatedEpoch
{
  get
  {
    DateTime epoch = new DateTime(1970, 1, 1, 0, 0, 0, 0).ToLocalTime();
    TimeSpan span = (this.Created.ToLocalTime() - epoch);
    return span.TotalSeconds;
  }
  set
  {
    DateTime epoch = new DateTime(1970, 1, 1, 0, 0, 0, 0).ToLocalTime();
    this.Created = epoch.AddSeconds(value);
  }
}

Source: (StackOverflow)

DateTime vs DateTimeOffset

Currently we have a standard way of dealing with .net DateTimes in a TimeZone aware way: Whenever we produce a DateTime we do it in UTC (e.g. using DateTime.UtcNow), and whenever we display one, we convert back from UTC to the user's local time.

That works fine, but I've been reading about DateTimeOffset and how it captures the local and UTC time in the object itself. So the question is, what would be the advantages of using DateTimeOffset vs what we have already been doing?

Source: (StackOverflow)

How do you set a default value for a MySQL Datetime column?

In SQL Server it's getdate(). What is the equivalant for MySQL? I'm using MySQL 5.x if that is a factor.

Source: (StackOverflow)

java.util.Date vs java.sql.Date

java.util.Date vs java.sql.Date: when to use which and why?

Source: (StackOverflow)

How do I calculate someone's age in C#?

Given a DateTime representing a person's birthday, how do I calculate their age?

Source: (StackOverflow)

How can relative time be calculated in C#?

Given a specific DateTime value, how do I display relative time, like:

2 hours ago
3 days ago
a month ago

Source: (StackOverflow)

Python Create unix timestamp five minutes in the future

I have to create an "Expires" value 5 minutes in the future, but I have to supply it in UNIX Timestamp format. I have this so far, but it seems like a hack.

def expires():
    '''return a UNIX style timestamp representing 5 minutes from now'''
    epoch = datetime.datetime(1970, 1, 1)
    seconds_in_a_day = 60 * 60 * 24
    five_minutes = datetime.timedelta(seconds=5*60)
    five_minutes_from_now = datetime.datetime.now() + five_minutes
    since_epoch = five_minutes_from_now - epoch
    return since_epoch.days * seconds_in_a_day + since_epoch.seconds

Is there a module or function that does the timestamp conversion for me?

Source: (StackOverflow)

How to get current datetime on Windows command line, in a suitable format for using in a filename?

What's a Windows command line statement(s) I can use to get the current datetime in a format that I can put into a filename?

I want to have a .bat file that zips up a directory into an archive with the current date and time as part of the name, for example, Code_2008-10-14_2257.zip. Is there any easy way I can do this, independent of the regional settings of the machine?

I don't really mind about the date format, ideally it'd be yyyy-mm-dd, but anything simple is fine.

So far I've got this, which on my machine gives me Tue_10_14_2008_230050_91:

rem Get the datetime in a format that can go in a filename.
set _my_datetime=%date%_%time%
set _my_datetime=%_my_datetime: =_%
set _my_datetime=%_my_datetime::=%
set _my_datetime=%_my_datetime:/=_%
set _my_datetime=%_my_datetime:.=_%

rem Now use the timestamp by in a new ZIP file name.
"d:\Program Files\7-Zip\7z.exe" a -r Code_%_my_datetime%.zip Code

I can live with this, but it seems a bit clunky. Ideally it'd be briefer and have the format mentioned earlier.

I'm using Windows Server 2003 and Windows XP Professional. I don't want to install additional utilities to achieve this (although I realise there are some that will do nice date formatting).

Source: (StackOverflow)

Best approach to remove time part of datetime in SQL Server

Which method provides the best performance when removing the time portion from a datetime field in SQL Server?

a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

or

b) select cast(convert(char(11), getdate(), 113) as datetime)

The second method does send a few more bytes either way but that might not be as important as the speed of the conversion.

Both also appear to be very fast, but there might be a difference in speed when dealing with hundreds-of-thousands or more rows?

Also, is it possible that there are even better methods to get rid of the time portion of a datetime in SQL?

Source: (StackOverflow)

Determine Whether Two Date Ranges Overlap

Given two date ranges, what is the simplest or most efficient way to determine whether the two date ranges overlap?

As an example, suppose we have ranges denoted by DateTime variables StartDate1 to EndDate1 and StartDate2 to EndDate2.

Source: (StackOverflow)

Compare two dates with JavaScript

Can someone suggest a way to compare the values of two dates greater than, less than, and not in the past using JavaScript? The values will be coming from text boxes.

Source: (StackOverflow)