public void FromDateTime_PureDate()
{
var date = new DateTime(2012, 12, 1, 0, 0, 0, DateTimeKind.Unspecified);
var unix = UnixTime.FromDateTime(date);
Assert.AreEqual(unix, "1354320000 +0000");
}
/// <summary>
/// 将指定fields中value为DateTime格式的值转为unix_time。若DateTime时间为00:00:00，则LessThan的时间自动加上23:59:59
/// </summary>
/// <param name="toUnixtime">是否将时间转为UnixtimeStamp.否则转为"yyyy-MM-dd HH:mm:ss"格式的字符串</param>
/// <param name="fields">要修改的字段名称</param>
/// <returns></returns>
public SearchModel DecorateDateTime(bool toUnixtime, params string[] fields)
{
if (this != null && this.Items != null && fields != null) //不为空
{
//遍历所有要修改的字段
this.Items.FindAll(t => fields.Contains(t.Field)).ForEach(e =>
{
DateTime value = new DateTime();
if (e.Value != null && DateTime.TryParse(e.Value.ToString(), out value))
{
//只设置了日期没有时间,截止日期加上23;59:59
if (value.Date == value && (e.Method == SearchMethod.LessThan || e.Method == SearchMethod.LessThanOrEqual))
{
value = value.AddDays(1).AddSeconds(-1);
}
if (toUnixtime)
{
e.Value = UnixTime.FromDateTime(value);
}
else
{
e.Value = value.ToString("yyyy-MM-dd HH:mm:ss");
}
}
});
}
return(this);
}
public IEnumerable <ConditionItem> Transform(ConditionItem item, Type type)
{
DateTime willTime;
if (DateTime.TryParse(item.Value.ToString(), out willTime))
{
var method = item.Method;
if (method == QueryMethod.LessThan || method == QueryMethod.LessThanOrEqual)
{
method = QueryMethod.DateTimeLessThanOrEqual;
if (willTime.Hour == 0 && willTime.Minute == 0 && willTime.Second == 0)
{
willTime = willTime.AddDays(1).AddMilliseconds(-1);
}
}
object value = null;
if (type == typeof(DateTime))
{
value = willTime;
}
else if (type == typeof(int))
{
value = (int)UnixTime.FromDateTime(willTime);
}
else if (type == typeof(long))
{
value = UnixTime.FromDateTime(willTime);
}
return(new[] { new ConditionItem(item.Field, method, value) });
}
return(new[] { new ConditionItem(item.Field, item.Method, Convert.ChangeType(item.Value, type)) });
}
[InlineData("1969-01-01 0:00:00", -31536000)] // Expect negative
public void FromDateTime_TestResults(string dtStr, int expected)
{
DateTime input = DateTime.Parse(dtStr);
int actual = UnixTime.FromDateTime(input);
Assert.Equal(expected, actual);
}
public void FromDateTime_DateAndTimeWithMilliseconds()
{
var date = new DateTime(2012, 12, 1, 13, 45, 12, 500, DateTimeKind.Unspecified);
var unix = UnixTime.FromDateTime(date);
Assert.AreEqual(unix, "1354369512 +0000");
}
public static DateTime RealTimeToEorzeaTime(DateTime realTime)
{
UnixTime actualTime = UnixTime.FromDateTime(realTime);
var eorzeaUnix = actualTime.Timestamp * EorzeaConstant;
var eorzeaTime = UnixTime.ToUniversalDateTime(eorzeaUnix);
return eorzeaTime;
}
public static DateTime EorzeaTimeToRealTime(DateTime eorzeaTime)
{
UnixTime actualTime = UnixTime.FromDateTime(eorzeaTime);
var realUnix = actualTime.Timestamp / EorzeaConstant;
var realTime = UnixTime.ToUniversalDateTime(realUnix);
return realTime;
}
static DefaultModelTransformers()
{
Dict = Dict ?? new Dictionary <string, Func <string, string> >
{
{
"ToUnixTime", c =>
{
DateTime dt;
if (DateTime.TryParse(c, out dt))
{
return(UnixTime.FromDateTime(dt).ToString());
}
return("0");
}
}
};
}
public void Add(string key, object value)
{
string strValue;
if (value == null)
{
strValue = null;
}
else if (value is string)
{
strValue = (string)value;
}
else if (value is Nullable <DateTime> )
{
Nullable <DateTime> dateTime = value as Nullable <DateTime>;
strValue = UnixTime.FromDateTime(dateTime.Value).ToString();
}
else if (value is Nullable <int> )
{
strValue = (value as Nullable <int>).Value.ToString();
}
else if (value is Nullable <long> )
{
strValue = (value as Nullable <long>).Value.ToString();
}
else if (value is Nullable <double> )
{
strValue = (value as Nullable <double>).Value.ToString();
}
else if (value is Nullable <bool> )
{
strValue = (value as Nullable <bool>).Value.ToString().ToLower();
}
else
{
strValue = value.ToString();
}
this.Add(key, strValue);
}
/// <summary>
///
/// </summary>
/// <param name="item"></param>
/// <param name="type"></param>
/// <returns></returns>
public IEnumerable <Filter> Transform(Filter item, Type type)
{
DateTime willTime;
if (DateTime.TryParse(item.Value.ToString(), out willTime))
{
var method = item.Operation;
if (method == OperationType.Less || method == OperationType.LessOrEqual)
{
method = OperationType.DateTimeLessThanOrEqual;
if (willTime.Hour == 0 && willTime.Minute == 0 && willTime.Second == 0)
{
willTime = willTime.AddDays(1).AddMilliseconds(-1);
}
}
object value = null;
if (type == typeof(DateTime))
{
value = willTime;
}
else if (type == typeof(int))
{
value = (int)UnixTime.FromDateTime(willTime);
}
else if (type == typeof(long))
{
value = UnixTime.FromDateTime(willTime);
}
return(new[] { new Filter {
Field = item.Field, Operation = method, Value = value
} });
}
return(new[] { new Filter {
Field = item.Field, Operation = item.Operation, Value = Convert.ChangeType(item.Value, type)
} });
}
public async Task Method2(TimeSpan offset)
{
//if (offset != DateTimeOffset.Now.Offset)
{
DatabaseFunction.SetDateOffset(offset);
}
using (var connection = this.database.Connect())
{
this.table1.Drop(connection);
await this.database.InitializeAsync(connection);
}
LibraryOwner.SetConfig(new LibraryConfiguration(""));
var items = new[]
{
new Record(GetId()),
new Record(GetId()),
new Record(GetId()),
};
var testName = "n[Am]e'j)(i%2@2_h'''l @p1";
items[0].SetName(testName);
var testName2 = testName.Replace('_', 'f').Replace(']', '-');
items[1].SetName(testName2);
var currentTime = DateTimeOffset.Now.ToOffset(offset);
items[1].DateModified = currentTime - TimeSpan.FromDays(2);
items[2].DateModified = currentTime;
await this.database.RequestTransactionAsync(async context =>
{
foreach (var item in items)
{
await this.table1.AddAsync(item, context);
}
await this.table1.AddAsync(new Record(testName), context);
await this.table1.AddAsync(new Record(testName2), context);
});
using (var connection = this.database.Connect())
{
Console.WriteLine("a");
//0001-01-01 00:00:00 +00:00
Assert.AreEqual("0",//"0001-01-01 00:00:00 +00:00",
this.table1.AsQueryable(connection)
.Select <string>(nameof(Record.DateModified))
.ToArray()
.First());
Console.WriteLine("b");
//0001-01-01
Assert.AreEqual((-offset.TotalSeconds).ToString(),
this.table1.AsQueryable(connection)
.Select <string>(DatabaseFunction.GetDate(nameof(Record.DateModified)))
.ToArray()
.First());
Console.WriteLine("c");
////00:00:00
//Assert.AreEqual("00:00:00",
// this.table1.AsQueryable(connection)
// .Select<string>($"time({nameof(Record.DateModified)})")
// .ToArray()
// .First());
//0001/01/01 0:00:00 +00:00
Assert.AreEqual(DateTimeOffset.FromUnixTimeSeconds(0), this.table1.AsQueryable(connection)
.Select <DateTimeOffset>(nameof(Record.DateModified))
.ToArray()
.First());
Console.WriteLine("d");
var today = DateTimeOffset.Now.ToOffset(offset).ToDate();
var mods = this.table1.AsQueryable(connection)
.Where(DatabaseExpression.AreEqual
(DatabaseFunction.GetDate(nameof(Record.DateModified)),
new DatabaseReference(UnixTime.FromDateTime(today).ToString())))
.Select <DateTimeOffset>($"{nameof(Record.DateModified)}")
.Take(2)
.ToArray();
Console.WriteLine("e");
Assert.AreEqual(1, mods.Length);
Console.WriteLine("f");
this.AreEqual(new DateTimeOffset(currentTime.Year, currentTime.Month, currentTime.Day,
currentTime.Hour, currentTime.Minute, currentTime.Second, offset),
mods[0]);
Console.WriteLine("g");
}
}
/// <summary>
/// Writes this field's value to a binary writer.
/// </summary>
/// <param name="writer">The binary writer where to write the field's value.</param>
protected override void WriteValue(BinaryWriter writer)
{
writer.Write(Convert.ToUInt32(UnixTime.FromDateTime(Value)));
}
public override void SetValue(IDbDataParameter parameter, DateTime?value) => parameter.Value = (value == null) ? null : (object)UnixTime.FromDateTime(value.Value);
public override void SetValue(IDbDataParameter parameter, DateTime value) => parameter.Value = UnixTime.FromDateTime(value);
[InlineData("1900-01-01 0:00:00")] //Too low
public void FromDateTime_OutOfRange_ExpectException(string dtStr)
{
DateTime input = DateTime.Parse(dtStr);
Assert.Throws <ArgumentOutOfRangeException>(() => UnixTime.FromDateTime(input));
}
private async Task TestUnixDate(DateTimeOffset date, IDbConnection connection, TimeSpan offset)
{
{
var dateNum = await table1.QueryAsync <long>
(connection, $"Select strftime('%s','{date.ToString("yyyy-MM-dd HH:mm:ss zzz")}')");
this.AreEqual(date.ToUnixTimeSeconds(), dateNum.FirstOrDefault());
}
this.AreEqual(date.ToUniversalTime().ToUnixTimeSeconds(), date.ToUnixTimeSeconds());
{
var dateNum = await table1.QueryAsync <long>
(connection, $"Select strftime('%s','{date.ToString("yyyy-MM-dd HH:mm:ss")}')");
if (date.Offset == default(TimeSpan))
{
this.AreEqual(date.ToUnixTimeSeconds(), dateNum.FirstOrDefault());
}
else
{
this.AreNotEqual(date.ToUnixTimeSeconds(), dateNum.FirstOrDefault());
}
}
{
var dateNum = await table1.QueryAsync <long>
(connection, $"Select strftime('%s','{date.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss")}')");
this.AreEqual(date.ToUnixTimeSeconds(), dateNum.FirstOrDefault());
}
{
var unixTime = $"strftime('%s','{date.ToString("yyyy-MM-dd HH:mm:ss zzz")}')";
var unixDate = DatabaseFunction.GetDate(unixTime);
var dateNum = await table1.QueryAsync <long>
(connection, $"Select {unixDate}");
if (DateTimeOffset.Now.Offset == offset)
{
this.AreEqual(DatabaseReference.DateOffsetReference(date).ToString(), dateNum.FirstOrDefault().ToString());
}
else
{
this.AreEqual(DatabaseReference.DateOffsetReference(date, offset).ToString(), dateNum.FirstOrDefault().ToString());
}
}
{
var unixTime = $"strftime('%s','{date.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss")}')";
var unixDate = DatabaseFunction.GetDate(unixTime);
var dateNum = await table1.QueryAsync <long>
(connection, $"Select {unixDate}");
if (DateTimeOffset.Now.Offset == offset)
{
this.AreEqual(DatabaseReference.DateOffsetReference(date.ToUniversalTime()).ToString(), dateNum.FirstOrDefault().ToString());
}
else
{
this.AreEqual(DatabaseReference.DateOffsetReference(date.ToUniversalTime(), offset).ToString(), dateNum.FirstOrDefault().ToString());
}
}
{
var container = new Table1 {
DateOffset = date
};
using (var tr = connection.BeginTransaction())
{
await table1.ReplaceAsync(container, connection, tr);
tr.Commit();
}
container.DateOffset = date.AddSeconds(1);
var dateNum = await table1.QueryAsync <string>
(connection, $"Select DateOffset FROM {table1.Name} WHERE (DateOffset+1) == @DateOffset", container);
Assert.AreEqual(1, dateNum.Count());
this.AreEqual(UnixTime.FromDateTime(date).ToString(), dateNum.FirstOrDefault());
}
}
public static DatabaseReference DateTimeOffsetReference(DateTimeOffset dateTime) => new DatabaseReference(UnixTime.FromDateTime(dateTime).ToString());
public static DatabaseReference DateOffsetReference(DateTimeOffset dateTime, TimeSpan offset) => new DatabaseReference(UnixTime.FromDateTime(dateTime.ToOffset(offset).ToDate()).ToString());
