PostgreSQL时间戳

DataGrip设置时区

DataGrip时区默认是 UTC+0, 不是UTC+8, 在连接属性里面设置UTC+8。

操作步骤

1. 右键打开你想要修改的数据库连接的Properties菜单；
2. 点击Advanced按钮；
3. 在VM options后面写入-Duser.timezone=Asia/Shanghai，就可以啦；
4. 断开数据库连接，重新执行一条SQL就可以看到效果了。

PG中时间戳类型的查询写法

时间戳存储

对于timestamp with time zone，内部存储的值总是 UTC （全球统一时间，以前也叫格林威治时间GMT）。
如果一个输入值有明确的时区声明， 那么它将用该时区合适的偏移量转换成 UTC。
如果在输入串里没有时区声明， 那么它就被假设是在系统的TimeZone参数里的那个时区，然后使用这个 timezone时区的偏移转换成 UTC。

双冒号语法

:: 操作符  ==> PostgreSQL风格的类型转换

在PostgreSQL数据库中，双冒号:: 是用于类型转换的，比如’2010-01-01’::date。

时间戳查询

SET TIMEZONE TO 'Asia/Shanghai';

select current_timestamp;

select '2021-12-18 19:53:49.985033+08'::timestamp with time zone;

select '2021-12-18 19:53:49+08'::timestamp with time zone;

select * from "Order" where "CreationTime" = '2021-12-19 15:49:31.210972+08'::timestamp with time zone;

select * from "Order"
where "PaymentTime"  
between '2021-12-19 15:13:10.024023+08'::timestamp with time zone
and '2021-12-19 16:45:15.108045+08'::timestamp with time zone;

程序中时间戳类型的选择

C#类型DateTime和DateTimeOffset的选择

在程序中统一使用UTC时间，http请求参数使用DateTimeOffset；
在参数解析绑定时，DateTimeOffset类型会自动带上本地时区的Offset，
而 DateTime 不会有时区信息，Kind属性值是DateTimeKind.Unspecified，

前端给后端传时间戳，后端收到转成utc时间(使用DateTimeOffset类型)，这样可以避免处理时区的问题。

[HttpGet]
[Route("date1")]
public async Task<JsonResult> DatetimeOffset1(DateTime dt)
{
    // http://localhost:9000/api/date1?dt=2021-12-19 15:13:10
    // "2021-12-19T15:13:10"

    var a = 1;

    return await Task.FromResult(new JsonResult(dt));
}

[HttpGet]
[Route("date2")]
public async Task<JsonResult> DatetimeOffset2(DateTimeOffset dt)
{
    // http://localhost:9000/api/date2?dt=2021-12-19 15:13:10
    // "2021-12-19T15:13:10+08:00"

    var a = 1;

    return await Task.FromResult(new JsonResult(dt));
}

JsonResult中DateTimeOffset的格式化

1. 编写自定义转换器

using System;
using System.Globalization;
using System.Text.Json;
using System.Text.Json.Serialization;

namespace Practical.Api.Helper
{
    public class DateTimeOffsetConverter : JsonConverter<DateTimeOffset>
    {
        public override DateTimeOffset Read(ref Utf8JsonReader reader, 
            Type typeToConvert, 
            JsonSerializerOptions options)
        {
            return DateTimeOffset
            .ParseExact(reader.GetString(), "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);
        }

        public override void Write(Utf8JsonWriter writer, 
            DateTimeOffset value, 
            JsonSerializerOptions options)
        {
            writer.WriteStringValue(value.ToLocalTime()
                .ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture));
        }
    }
}

2. StartUp类中配置序列化选项

public void ConfigureServices(IServiceCollection services)
{

    services.AddControllers()
    .AddJsonOptions(op => op.JsonSerializerOptions
                            .Converters.Add(new DateTimeOffsetConverter()));
    services.AddSwaggerGen(c =>
    {
        c.SwaggerDoc("v1", new OpenApiInfo { Title = "Practical.API", Version = "v1" });
    });

    RegisterMediatR(services);
}

3. Newtonsoft.Json库使用JsonSerializerSettings进行设置

var settings = new JsonSerializerSettings()
{
    DateFormatString = "yyyy-MM-dd HH:mm:ss",
    DateTimeZoneHandling = DateTimeZoneHandling.Local
};

参考文章：
https://docs.microsoft.com/en-us/dotnet/standard/serialization/system-text-json-converters-how-to?pivots=dotnet-6-0#sample-basic-converter

JS时间戳的转换

首先要清楚JavaScript与Unix的时间戳的区别：

JavaScript时间戳：是指格林威治时间1970年01月01日00时00分00秒(北京时间1970年01月01日08时00分00秒)起至现在的总毫秒数。

Unix时间戳：是指格林威治时间1970年01月01日00时00分00秒(北京时间1970年01月01日08时00分00秒)起至现在的总秒数。

可以看出JavaScript时间戳总毫秒数，Unix时间戳是总秒数。

比如同样是的 2016/11/03 12:30:00 ，转换为JavaScript时间戳为 1478147400000；转换为Unix时间戳为 1478147400。

1. C# DateTime转换为JavaScript时间戳

 // 当地时区
System.DateTime startTime = TimeZone.CurrentTimeZone
                                    .ToLocalTime(new System.DateTime(1970, 1, 1));
long timeStamp = (long)(DateTime.Now - startTime).TotalMilliseconds; // 相差毫秒数
System.Console.WriteLine(timeStamp);

2. JavaScript时间戳转换为C# DateTime

long jsTimeStamp = 1478169023479;
 // 当地时区
System.DateTime startTime = TimeZone.CurrentTimeZone
                                    .ToLocalTime(new System.DateTime(1970, 1, 1));
DateTime dt = startTime.AddMilliseconds(jsTimeStamp);
System.Console.WriteLine(dt.ToString("yyyy/MM/dd HH:mm:ss:ffff"));

var udt1 = DateTimeOffset.FromUnixTimeSeconds(1478147400);
var udt2 = DateTimeOffset.FromUnixTimeMilliseconds(1478147400000);

3. JavaScript获取时间戳与时间戳转化

Javascript 获取当前时间戳（毫秒级别）：

第一种方法：结果：1470220594000

var timestamp1 = Date.parse( new Date());


第二种方法：结果：1470220608533

var timestamp2 = ( new Date()).valueOf();


第三种方法：结果：1470220608533

var timestamp3 = new Date().getTime();

第一种获取的时间戳是精确到秒。
第二种和第三种是获取的时间戳精确到毫秒。

获取时间戳的方法还有：
+new Date();
Date.now(); // 这个ES5才添加的方法
还有 new Date - 0;

---

获取指定时间的时间戳：

new Date("2016-08-03 00:00:00");

时间戳转化成时间：

function timetrans(date){
    var date = new Date(date*1000);//如果date为13位不需要乘1000
    var Y = date.getFullYear() + '-';
    var M = (date.getMonth()+1 < 10 ? '0'+(date.getMonth()+1) : date.getMonth()+1) + '-';
    var D = (date.getDate() < 10 ? '0' + (date.getDate()) : date.getDate()) + ' ';
    var h = (date.getHours() < 10 ? '0' + date.getHours() : date.getHours()) + ':';
    var m = (date.getMinutes() <10 ? '0' + date.getMinutes() : date.getMinutes()) + ':';
    var s = (date.getSeconds() <10 ? '0' + date.getSeconds() : date.getSeconds());
    return Y+M+D+h+m+s;
}